Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Is there a way to crop text in an excel cell Results

Need to find a way to crop text in an Excel column. Want to crop any text
past characters. Is there a way to do this?

I realize that several people have asked about topics similar to my issue, and I have read many threads here and elsewhere but have not found a solution.

Since this site has a lot of resources, I have tried using them. Of most relevance were these two How Tos from this site:
Excel VBA: Delete Excel Rows Based on a Specified Condition or Criteria
How to use AutoFilters in Excel VBA Macros

The first article seemed like it would be exactly what I need, but the EntireRow.Delete call used in that example is the line that is generating my error.

In brief, I am opening two external files, copying some data from each into it's own tab of data in the original file, then for each set of pasted data, trying to delete any row that has "Obsolete" in the 8th column.

Here is a longer explanation of what I'm doing:
I have three files as part of the code:

The original file, which contains the macro
ProdFamily_Lookup: This tab is used by the user to enter a part number, and vlookups are performed on the other tabs to return a product familySAP_Dump: This tab is one set of lookup data that comes from the first file opened by the macro; the data is a named Table "SAP_Table"SAP_Dump_5xx: This tab is a second set of lookup data that comes from the second file opened by the macro; the data is a named Table "SAP5xx_Table"SAP_Dump.xlsx: This file contains the first set of lookup data that will be copied and pasted into the original file (one tab, named to the date the dump file was created)SAP_Dump_5xx.xlsx: This file contains the second set of lookup data that will be copied and pasted into the original file (one tab, named to the date the dump file was created)The basic steps in the macro are to, for each set of data to be copied/pasted:

Disable any existing AutoFilterCrop the Table of data (this is easier than deleting the table and recreating it each time)Open the external fileCopy the first 11 columns of dataPaste the copied data (paste values) starting in cell A2 (to maintain the table headers)Close the external fileFilter column 8 match a value of "Obsolete"Delete all visible rows of the filtered TableRemove the AutoFilterRepeat for the second set of data
The external files are quite large (5MB and 18MB respectively), so the macro will take a little while to run. The way the data is dumped, I can't just copy the entire tab from the external files because the data thinks that it extends to column "XFD" and is so large that Excel freaks out (someone else controls the data dump so I can't get this fixed). So I decided to just copy the columns I need and paste them into the lookup tabs.

Here is my code:

	VB:
	
 SAP_Dump_Refresh() 
     '
     ' SAP_Dump_Refresh Macro
     '
     
     '****************************************************************************************
     '**     Macro to update the two SAP Dump tabs from external Dump files                 **
     '****************************************************************************************
     
     '****************************************************************************************
     '**     Macro prep work:                                                               **
     '**       - define vatiables                                                           **
     '**       - tell the user the macro is running                                         **
     '**       - disable screen updates                                                     **
     '****************************************************************************************
     
    Dim wbSAP As Workbook 
    Dim wbSAP5xx As Workbook 
    Dim wbLookup As Workbook 
    Dim rSAPRange As Range 
    Dim rSAP5xxRange As Range 
     
    Set wbLookup = ThisWorkbook 
     
    Sheets("ProductFamily_Lookup").Activate 
    Range("WaitMsgCell").Select 
    ActiveCell.Formula = "Macro is updating, please wait..." 
     
     'Application.ScreenUpdating = False
     
     '****************************************************************************************
     '**     First prepare the file by cropping the existing tables which will be replaced  **
     '****************************************************************************************
     
    Sheets("SAP_Dump").Activate 
    ActiveSheet.AutoFilterMode = False 
    Range("7:7").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Delete Shift:=xlUp 
     
    Sheets("SAP_Dump_5xx").Activate 
    ActiveSheet.AutoFilterMode = False 
    Range("7:7").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Delete Shift:=xlUp 
     
     
     '****************************************************************************************
     '**     Next step is to open the two external files and for each:                      **
     '**      - copy/paste the first 11 columns of data                                     **
     '**        (the columns needed are Material [1] and Part Test Family [11])             **
     '**      - delete all rows with Obsolete in the Status [8] column                      **
     '****************************************************************************************
     
    Set wbSAP = Workbooks.Open("C:UsersjmengelDesktopReferenceSAP_Dump.xlsx", True, True) 
     
    wbSAP.Activate 
    ActiveSheet.AutoFilterMode = False 
    ActiveSheet.Range("A2:K2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Copy 
     
    wbLookup.Activate 
    Sheets("SAP_Dump").Activate 
    Range("A2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
     
    wbSAP.Close savechanges:=False 
    wbLookup.Activate 
    Sheets("SAP_Dump").Activate 
     
    Set rSAPRange = ActiveSheet.Range("SAP_Table") 
    ActiveSheet.AutoFilterMode = False 
    With rSAPRange 
        .AutoFilter 
        .AutoFilter Field:=8, Criteria1:="Obsolete" 
         '************************************************************************************
         '********* This line causes run-time error '1004' delete method of range class failed
         '************************************************************************************
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
    ActiveSheet.AutoFilterMode = False 
     
    Range("C2").Select 
     
    Set wbSAP5xx = Workbooks.Open("C:UsersjmengelDesktopReferenceSAP_Dump_5xx.xlsx", True, True) 
     
    wbSAP5xx.Activate 
    Range("A2:K2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Copy 
    wbLookup.Activate 
    Sheets("SAP_Dump_5xx").Select 
    Range("A2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
     
    Set rSAP5xxRange = ActiveSheet.Range("SAP_5xx_Table") 
    ActiveSheet.AutoFilterMode = False 
    With rSAP5xxRange 
        .AutoFilter 
        .AutoFilter Field:=8, Criteria1:="Obsolete" 
         '************************************************************************************
         '********* This line causes run-time error '1004' delete method of range class failed
         '************************************************************************************
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
    ActiveSheet.AutoFilterMode = False 
     
    Range("C2").Select 
     
    wbSAP5xx.Close savechanges:=False 
    wbLookup.Activate 
     
     
     '****************************************************************************************
     '**     Macro clean up:                                                                **
     '**       - remove the macro is running text                                           **
     '**       - return cursor to part number cell                                          **
     '**       - enable screen updates                                                      **
     '****************************************************************************************
     
    Sheets("ProductFamily_Lookup").Activate 
    Range("WaitMsgCell").Select 
    ActiveCell.Clear 
    Range("C2").Select 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
There are two problems that I'm noticing. The first, which is not a show-stopper, is that the ActiveSheet.AutoFilterMode = False is not turning off the AutoFilter. For debugging, I've commented out the ScreenUpdating = False call, so I can watch what is happening. At no time, on any of the tabs, does the AutoFilter actually turn off. This is not a major issue, but I mention it in case it is linked to the root cause of the second, more pressing problem.

The second problem is that the EntireRow.Delete call is causing a "run-time error '1004' delete method of range class failed" alarm. This was taken, almost directly, from the first How To link above.

It might be relevant that the data sets from which I am filtering and deleting rows are named Tables. If Tables need to be handled differently, that is probably what I'm doing wrong. I'm also not an expert at macros; I've tried researching this and have found many sggestions and have tried to implement them as I could. So if my code seems a little haphazard, that's why. Any insight into my issue (or even suggestions for more elegant methods/practices) would be appreciated.

~Jason


No luck finding an answer? You could always try Google.