Defining a Variable Length Range in VBA

[serubenstein]

I am new to VBA / Excel Macros and would like to request some programming assistance.

I have defined a counter for the number of rows in a spreadsheet (rowCount) using the CountA worksheet function.

My next step is to define a range using this value (# of rows) to perform several operations like formating the column and copying a formula down the length of the column.

This is where I am currently (trying to define the format to the range) which isn't the correct syntax:

Range("AB2").Select
Range("AB2", "AB(rowCount)").Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"

Any assistance would be greatly appreciated.

Thank you.

 

[Jonmo1]

Welcome to the board...

Try

Range("AB2", Range("AB" & rowCount)).NumberFormat = "[$-409]d-mmm-yy;@"