Help on Macro to hide empty rows
 

Hi, I have a spreadsheet I created for an administrator that has many extra rows with pre-set formulas. When we print though, there are a lot of empty rows in between the relevant data. I am trying to build a macro that will hide any row where column A is empty, then print, and then unhide them again. Below is the macro I have so far. But it does nothing! Any help or suggestions are appreciated as I haven't written macros in years. (I have latest version of Excel on Windows Vista.)

Sub PrintOrmondBeach()
'
' PrintOrmondBeach Macro
Sheets("Ormond").Select
Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Select
For x = 1 To 300
If Ax = " " Then
Selection.EntireRow.Hidden = True
' Selects cell down 1 row from active cell.
End If
ActiveCell.Offset(1, 0).Select
Next
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Print").Select
End Sub

Help on Macro to hide empty rows
 

Option Explicit
Sub PrintOrmondBeach()

Dim myCell As Range
Dim myRng As Range

With Worksheets("Ormond")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
myCell.EntireRow.Hidden = True
End If
Next myCell

.PrintOut
End With

End Sub

I wouldn't be surprised that the cells in column A that look empty don't really
contain space characters. I'd bet that they were really empty.



nLeBlanc21 wrote:

Hi, I have a spreadsheet I created for an administrator that has many
extra rows with pre-set formulas. When we print though, there are a lot
of empty rows in between the relevant data. I am trying to build a macro
that will hide any row where column A is empty, then print, and then
unhide them again. Below is the macro I have so far. But it does
nothing! Any help or suggestions are appreciated as I haven't written
macros in years. (I have latest version of Excel on Windows Vista.)

Sub PrintOrmondBeach()
'
' PrintOrmondBeach Macro
Sheets("Ormond").Select
Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Select
For x = 1 To 300
If Ax = " " Then
Selection.EntireRow.Hidden = True
' Selects cell down 1 row from active cell.
End If
ActiveCell.Offset(1, 0).Select
Next
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Print").Select
End Sub

--
nLeBlanc21

--

Dave Peterson

Thank you so much Dave! It actually works! You have no idea how much better this macro will make everything. If I could bother you one more time though, I recorded a macro to unhide all the rows in the worksheet after it is printed. That way the administrator can print the worksheet without all the extra rows, but still have all the rows open to enter information after she has printed it. When I pasted my recorded macro into the macro you wrote, it didn't work. Again, thank you so much for your time and help!

Help on Macro to hide empty rows
 

Option Explicit
Sub PrintOrmondBeach()

Dim myCell As Range
Dim myRng As Range

With Worksheets("Ormond")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
myCell.EntireRow.Hidden = True
End If
Next myCell

.PrintOut

.UsedRange.Rows.Hidden = False '<-- added

End With

End Sub

nLeBlanc21 wrote:

Thank you so much Dave! It actually works! You have no idea how much
better this macro will make everything. If I could bother you one more
time though, I recorded a macro to unhide all the rows in the worksheet
after it is printed. That way the administrator can print the worksheet
without all the extra rows, but still have all the rows open to enter
information after she has printed it. When I pasted my recorded macro
into the macro you wrote, it didn't work. Again, thank you so much for
your time and help!

--
nLeBlanc21

--

Dave Peterson

Works perfectly! Thanks :)