Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple - Del empties
Hi,
I would like a code to delete empty rows based on column E (row 4 of the table). Using the recorded I apply a filter, use the filter to show the blank cells from col E. At this point I dont know how to ask VB to select the first row in table - it could be any row from 1 to 66500 depending on the contents. How I get it to select the first empty row rather than having to ask it to select a certain row as in the code below? Once selected I want to delete all rows from there to the end. Thanks Sub DELROWS() ' Rows("2:2").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="=" Rows("9:9").Select Range("B9").Activate Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.delete Shift:=xlUp Selection.AutoFilter Field:=4 Rows("2:2").Select Selection.AutoFilter Range("B1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple - Del empties
for rw = range("E65000").Exd(xlup).Row to 4 step -1
if cells(rw,"E")="" then rows(rw),delete end if next "LiAD" wrote: Hi, I would like a code to delete empty rows based on column E (row 4 of the table). Using the recorded I apply a filter, use the filter to show the blank cells from col E. At this point I dont know how to ask VB to select the first row in table - it could be any row from 1 to 66500 depending on the contents. How I get it to select the first empty row rather than having to ask it to select a certain row as in the code below? Once selected I want to delete all rows from there to the end. Thanks Sub DELROWS() ' Rows("2:2").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="=" Rows("9:9").Select Range("B9").Activate Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.delete Shift:=xlUp Selection.AutoFilter Field:=4 Rows("2:2").Select Selection.AutoFilter Range("B1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple - Del empties
Does this line of code do what you want?
Range("E4:E" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireR ow.Delete -- Rick (MVP - Excel) "LiAD" wrote in message ... Hi, I would like a code to delete empty rows based on column E (row 4 of the table). Using the recorded I apply a filter, use the filter to show the blank cells from col E. At this point I dont know how to ask VB to select the first row in table - it could be any row from 1 to 66500 depending on the contents. How I get it to select the first empty row rather than having to ask it to select a certain row as in the code below? Once selected I want to delete all rows from there to the end. Thanks Sub DELROWS() ' Rows("2:2").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="=" Rows("9:9").Select Range("B9").Activate Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.delete Shift:=xlUp Selection.AutoFilter Field:=4 Rows("2:2").Select Selection.AutoFilter Range("B1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple - Del empties
Perfect and simple.
Thanks "Rick Rothstein" wrote: Does this line of code do what you want? Range("E4:E" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireR ow.Delete -- Rick (MVP - Excel) "LiAD" wrote in message ... Hi, I would like a code to delete empty rows based on column E (row 4 of the table). Using the recorded I apply a filter, use the filter to show the blank cells from col E. At this point I dont know how to ask VB to select the first row in table - it could be any row from 1 to 66500 depending on the contents. How I get it to select the first empty row rather than having to ask it to select a certain row as in the code below? Once selected I want to delete all rows from there to the end. Thanks Sub DELROWS() ' Rows("2:2").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="=" Rows("9:9").Select Range("B9").Activate Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.delete Shift:=xlUp Selection.AutoFilter Field:=4 Rows("2:2").Select Selection.AutoFilter Range("B1").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple - Del empties
nice Rick - I should have thought of that!
"Rick Rothstein" wrote: Does this line of code do what you want? Range("E4:E" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireR ow.Delete -- Rick (MVP - Excel) "LiAD" wrote in message ... Hi, I would like a code to delete empty rows based on column E (row 4 of the table). Using the recorded I apply a filter, use the filter to show the blank cells from col E. At this point I dont know how to ask VB to select the first row in table - it could be any row from 1 to 66500 depending on the contents. How I get it to select the first empty row rather than having to ask it to select a certain row as in the code below? Once selected I want to delete all rows from there to the end. Thanks Sub DELROWS() ' Rows("2:2").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="=" Rows("9:9").Select Range("B9").Activate Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.delete Shift:=xlUp Selection.AutoFilter Field:=4 Rows("2:2").Select Selection.AutoFilter Range("B1").Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple - Del empties
change
if cells(rw,"E")="" then to if cells(rw,"E").Interior.Colorindex = ???? then to delete rows where cells are shaded ??? ??? is a LONG number "Patrick Molloy" wrote: for rw = range("E65000").Exd(xlup).Row to 4 step -1 if cells(rw,"E")="" then rows(rw),delete end if next "LiAD" wrote: Hi, I would like a code to delete empty rows based on column E (row 4 of the table). Using the recorded I apply a filter, use the filter to show the blank cells from col E. At this point I dont know how to ask VB to select the first row in table - it could be any row from 1 to 66500 depending on the contents. How I get it to select the first empty row rather than having to ask it to select a certain row as in the code below? Once selected I want to delete all rows from there to the end. Thanks Sub DELROWS() ' Rows("2:2").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:="=" Rows("9:9").Select Range("B9").Activate Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.delete Shift:=xlUp Selection.AutoFilter Field:=4 Rows("2:2").Select Selection.AutoFilter Range("B1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA empties clipboard | Excel Programming | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Office Clipboard empties itself when enabling/disabling commandbars | Excel Programming | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel |