Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing rows but not based on a range
Hello:
Below, I have code to which I have added to a macro to remove rows that contain either the word "Grand" or "#VALUE!" in column A. I got this code by "cheating". Specifically, I mimicked the use of Excel's Auto Filtering in a macro. This is the last piece of a very big puzzle that I have spent over two days working on. (I did not realize that this would take so long.) What I need to end this (hopefully once and for all) is to remove the two ranges that are mentioned in the code below. You see, I need for this filtering to remove rows meeting the "Grand" and "#VALUE!" criteria that I mentioned earlier in this posting. You see, I don't have a specific range in mind. I just need for Excel to use in its auto filtering in the code below to remove rows at the end of my spreadsheet that say either "Grand" or "#VALUE!" in cells in column A. Right now, as you can see from the code below, "Grand" appears in row A667 while "#VALUE!" appears beginning in row A668. Next time, though, this data may appear in A800, A450, A900, Awhatever.....I don't have a set range in mind. I don't know VBA syntax very well, but I'm guessing that it's just a matter of taking out "("A668:D668")" and ("A667:D667") and replacing these two lines of the code with whatever syntax that says "any row meeting this criteria". Any help would be much appreciated! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="#VALUE!" Range("A668:D668").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1, Criteria1:="Grand" Range("A667:D667").Select Selection.EntireRow.Delete Selection.AutoFilter Field:=1 Cells.Select Selection.Copy Sheets("Sheet3").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
Removing rows based on isDate | Excel Programming | |||
Moving rows with out removing from range | Excel Programming | |||
Finding criteria and removing matching rows (Range issue?) | Excel Programming | |||
Removing 1st four rows from a range | Excel Programming |