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

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

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

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

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

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