Hiding Rows

[Excel-ent]

Hi,

I have been told by the Experts that to hide the row containing zeros or blanks, I can filter and then the remaining cells can be copies to another sheet by Edit Copy –new sheet - Edit Paste special – visible cells only.

A macro for the above would be most appreciated.

If Zero or blank cell is found from G12 - G65536, then the those rows to be hidden. The visible cells only to be transferred to a new sheet.

I am using Windows 98 & Office 2000
Best Regards,


Shan

[Zack Barresse]

hi Shan,

how about recording a macro? this will make your desired code for you.

i would do an advanced filter for anything greater than zero, go to (F5) visible cells only (as you stated), copy, navigate, paste special (values, formats, formulas, choose your poison).

you can then see your relevant code by pressing Alt + F11 (shortcut to the Visual Basic Editor), finding your workbook, and looking in the Modules folder (left frame, code in right frame).

hth

[erik.van.geit]

Hello,
found in archives
don't know the original author
I changed it a bit

This macro will hide all of the blank rows in the active worksheet or in the selection if covering more than one row.

Public Sub HideBlankRows()

Dim R As Long
Dim C As Range
Dim n As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For R = rng.Rows.Count To 1 Step -1
MsgBox R & " " & Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow)
If Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Hidden = True
n = n + 1
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

regards,
Erik