VBA dynamically format Row1

[Liz]

Hello All -

Thanks in advance for any help. Background info: Excel 2003 Sp1

I am trying to create a macro\subroutine to format the first row in a
spreadsheet. Of course I can get the formatting syntax by recording a macro
as I format. The problem is dynamically identifying the last column used. I'd
like to use this macro for all of my spreadsheets so the number of columns
will vary.

For example;
spreadsheet1
A1=FirstName
B1=LastName
C1=SS#

spreadsheet2
A1=Category
B1=SubCategory

So running the macro in either spreadsheet would set the background to gray,
bold, horizontal, wordwrap, etc just for the columns that contain data. In
spreadsheet 1 that would be 3 cells (A1,B1,C1) and spreadsheet 2 2
cells(A1,B1).

If you can provide a snip-it of code that would be appreciated.

Thanks in advance,
Liz

 

[Bob Phillips]

Set rng = Range("A1").Resize(,Cells(1,Columns.Count).End(xlToLeft).Column)

then just format using the variable rng object.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken B]

Thanks in advance for any help. Background info: Excel 2003 Sp1