How to use Ctrl+Shift+xldown in code

[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

 

[Ron de Bruin]

This will give you the last row in column A
Range("A" & Rows.Count).End(xlUp).Row

Try this macro for the usedrange(if you only have data in Column A)

Sub DisplayUsedrangeOnly()
'// Dana DeLouis
Dim Rng As Range
Set Rng = ActiveSheet.UsedRange

Application.Goto Rng, True
On Error Resume Next

With Rng.EntireColumn
.Hidden = True
Rng(1).EntireRow.SpecialCells(xlVisible).EntireColumn.Hidden = True
.Hidden = False
End With

With Rng.EntireRow
.Hidden = True
Rng(1).EntireColumn.SpecialCells(xlVisible).EntireRow.Hidden = True
.Hidden = False
End With
Rng(1).Select
End Sub

Hi

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:

Sub
ActiveCell.Cells.Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Selection.End(xlDown).Select
Range("A" & ActiveCell.Row + 1).Select
****************************
Selection.End(xlDown).Select
Selection.EntireRow.Hidden = True
End Sub

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.

 

[Russell Hauf]

You can actually do this with one line of code. Try something lik
this
Code

 

[Vasant Nanavati]

Or you can just skip all the ActiveSheet prefixes and make it even shorter
<g>.

As long as the code is in a standard module, it will consider unqualified
range references to be on the active sheet by default.

 

[Guest]

Here is the code I would use...
Range("A1", Range("A1").End(xlDown)).Select

-----Original Message-----
Hi

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:

Sub
ActiveCell.Cells.Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Selection.End(xlDown).Select
Range("A" & ActiveCell.Row + 1).Select
****************************
Selection.End(xlDown).Select
Selection.EntireRow.Hidden = True
End Sub

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.