Supressing blank cells in VBA

[Guest]

H

Firstly, a bit of background about what I'm trying to do

I've got a Word mailmerge and the datasource is in excel. I'm trying to write a bit of code which goes to the last row in excel, and then hides all of the blank rows that appear after this. The code that I have done already is

Su
ActiveCell.Cells.Selec
Cells.Selec
Selection.EntireRow.Hidden = Fals
Range("A1").Selec
Selection.End(xlDown).Selec
Range("A" & ActiveCell.Row + 1).Selec
****************************
Selection.End(xlDown).Selec
Selection.EntireRow.Hidden = Tru
End Su

Where **** is, is where I'm stuck. The last row on the spreadsheet is variable which is why I use Ctrl+xlDown to get to it from "A1". If I was doing this manually, I'd press Ctrl+Shift+xlDown which would highlight the rest of the spreadsheet, then I could format/rows and hide the rows that appear after the last record. Does anyone know how I can do this in code? When I record a macro it just gives me the range that I've selected but I need some code that will go from the active cell, to the end of the spreadsheet.

 

[alcorjr]

I don't know if I'm getting your question right, but this script will
give you the last row with data in your range (column A in this case)
:

Dim i as long
i= ActiveSheet.Range("A64000").End(xlUp).Row



Hope it helps

 

[Dave Peterson]

I like to use a couple of variables:

Option Explicit
sub testme01()
dim TopCell as range
Dim botCell as range

with activesheet
set topcell = .range("a1").end(xldown).offset(1,0)
set botcell = .cells(.rows.count,"A")
.range(topcell,botcell).entirerow.hidden = true
end with
end sub

Actually, I'd start at the bottom and come up:

Option Explicit
sub testme02()
dim TopCell as range
Dim botCell as range

with activesheet
set topcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
set botcell = .cells(.rows.count,"A")
.range(topcell,botcell).entirerow.hidden = true
end with
end sub

=======
But if I remember mailmerge correctly, you can limit your input rows there. You
can specify the non-blank (kind of like Data|Filter|autofilter would do).

And you didn't ask, but here's a couple of links that you may want to review:

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.