A client has a spreadsheet with some rows set at either zero or close to
zero height and he wants to copy only those (what he calls)"hidden" rows to
another sheet for comparison to different data. I realize that he should
not have done it this way (better to have filtered the data) but he does not
want to go back and unhide and mark each row. Any way to create VBA - or
maybe an easier way - that looks at the row height property and returns the
values??

TIA

Hi CY,

The following macro copies to a separate sheet all the rows with height less
than 5 plus hidden rows. I did it this way because it is possible that your
client has inadvertantly hidden rows mixed with ones of minimum height.
Adjust the minimum height of row if required.

Selecting the destination rows has been done with a method which does not
require the cells in column 1 to have data because I don't know if all cells
in column 1 have data.

You will need to edit the line which sets the range of rows to look at. Hard
to use alternative methods such as UsedRange or the End(xlUp) method because
with hidden rows it depends where they are whether they will cause a problem
using these alternative methods.

If you have any problems with it then feel free to get back to me.

Sub Macro1()
Dim rngSht1 As Range
Dim r As Range
Dim wsSht1 As Worksheet
Dim wsSht2 As Worksheet
Dim sht2Row As Single

Set wsSht1 = Sheets("Sheet1")
Set wsSht2 = Sheets("Sheet2")

'Set max rows to include all the data.
Set rngSht1 = wsSht1.Rows("1:30")

sht2Row = 2
For Each r In rngSht1.Rows
If r.RowHeight < 5 Or r.Hidden = True Then
r.EntireRow.Copy Destination:= _
wsSht2.Cells(sht2Row, 1)
sht2Row = sht2Row + 1
End If
Next r

wsSht2.Cells.EntireRow.Hidden = False
wsSht2.Cells.Rows.AutoFit

End Sub


Regards,

OssieMac





"CY" wrote:

A client has a spreadsheet with some rows set at either zero or close to
zero height and he wants to copy only those (what he calls)"hidden" rows to
another sheet for comparison to different data. I realize that he should
not have done it this way (better to have filtered the data) but he does not
want to go back and unhide and mark each row. Any way to create VBA - or
maybe an easier way - that looks at the row height property and returns the
values??

TIA