Excel VBA: How to select only rows containing data?

[Russell Somers]

Hi all,

I'm writing a macro to automate formatting of a commonly used report.
The number of rows containing data will vary on any given day. I'd
like to select only the rows that contain data, set the heights for
those rows to 63.75, and leave the other rows untouched.

Does anyone have a suggestion on how to select only rows containing
data? I'm running Excel 2000 on Windows 2000

Best regards,
Russell Somers

 

[Don Guillett]

try
Sub setrowht()
For Each c In Selection
If c <> "" Then c.RowHeight = 63.75
Next
End Sub

 

[Bob Phillips]

Russell,

You need a column to test for. Let's assume A here, this selects all of
those cells

Range("A1:A" & Cells(Rows.Count,"A").End(xlUp).Row).Select

and this sets the height for the required rows

Rows("1:" & Cells(Rows.Count, "A").End(xlUp).Row).RowHeight = 63.75

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul B]

Russell, you want have to select the range with this one, modified form
Don's
Sub setrowht()
For Each c In ActiveSheet.UsedRange.Cells
If c <> "" Then c.RowHeight = 63.75
Next
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Dana DeLouis]

Perhaps just another way. Cells looks at the entire sheet. Adjust to your
range if required...

Sub Demo()
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas).EntireRow.RowHeight = 63.75
Cells.SpecialCells(xlCellTypeConstants).EntireRow.RowHeight = 63.75
End Sub