How to build a range from the last occupied cell in column? 1

[Rexxx]

What is the VB command to build a range (in Excel) beginning with the last occupied cell in a certain column plus 170 rows?

 

[DaleWatson123321]

Hi Rexxx,

This routine will do the job...

It requires that you first assign a range name to the "certain column" you refer to. I've used the name "cert_colm".

Then, when you run the routine, it will:
a) assign the range name "cert_range" to the range you requested, and
b) highlight that range in yellow.

You will probably want to modify these names and color to suit your own needs. They are only intended as examples.

Sub Set_Range()
Application.Goto Reference:="cert_colm"
curcolm = ActiveCell.Column
FirstCell = Cells(65536, curcolm).End(xlUp).Address
LastCell = Cells(65536, curcolm).End(xlUp).Offset(170, 0).Address
Data_Range = FirstCell & ":" & LastCell
Range(Data_Range).Name = "cert_range"
Application.Goto Reference:="cert_range"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

I hope this is what you wanted. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca