Range in macro

[Beth]

I'm recording a macro to delete blank rows. I turn on the
AutoFilter, filter on equals "", go down one row to begin
selection, then Shift+End+Down to select everything. Then
delete rows. It works now, but will it when the first row
in the results of the autofilter is not row 560? How can I
fix this code to replace ("D560") with a variable?

Columns("D").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=""""",
Operator:=xlAnd
Range("D560").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Range("E1").Select
End Sub

 

[Bob Phillips]

Beth,

Very simply

Dim cell as Range

set cell = Range("D560")
myMacro cell

Sub myMacro(cell As Range)
Columns("D").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=""""", Operator:=xlAnd
Range(cell, cell.End(xlDown)).EntireRow.Delete
Selection.AutoFilter
Range("E1").Select
End Sub

 

[Beth]

Thanks for the response. But isn't my range still set to
D560? I want it to be whatever the first row in the
filter's results are, not necessarily row 560. When I do
it manually, I just down-arrow once from the heading cell,
D1. I tried using that down-arrow combination with
SendKeys, but couldn't get syntax right.

 

[Bob Phillips]

Beth,

Yes it does, I am afraid I didn 't realise that for variable you meant first
applicable.

Do you really need VBA, or couldn't you use Ken's suggestion?

 

[Beth]

I really do need VBA. We have a large and complex and very
ugly worksheet maintained by our client and posted to our
terminal server daily. Our on-call tech support staff
needs to print a sorted, filtered, compressed list from
this spreadsheet. Rather than relayout the entire sheet
daily, I'm writing a macro for all user to apply as
needed. Asking the client to relayout the sheet is not an
option.

So far, using macros, I'm able to sort the whole sheet,
hide unneeded columns, resize columns, set row height to
autofit, set page layout settings and send to default
printer. Now I'd like to get rid of all the blank rows at
the bottom of the sort... That's where this little macro
comes in.

 

[Bob Phillips]

Beth,

Okay, how about this alternative method?

Dim oRange As Range

Set oRange = Range("D1", Cells(Rows.Count, "D").End(xlUp))
oRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Really it's just Ken's solution macro-ised.

 

[TheGrinch]

Something like this may be more fitting

Sub DeleteZeros()
'

Sheets("Sheet1").Select
Application.ScreenUpdating = False
For Each c In Range("d1:d1000")
If c.Value = "" Then
c.EntireRow.delete
Application.ScreenUpdating = True
End If
End If
Next
End Su