Set print range with VBA

skywaz

New Member
Joined
Sep 6, 2002
Messages
21
Using the before print routine in Excel 02, I am trying to set the print range to contain cells with data, and exclude those that are blank. I have found several useful threads on how to determine the last used cell in a worksheet, but the worksheet in question is a (3) page template and depending on user input, may have several rows blank. The blank rows have conditional formatting, but no data. Using the above technique, Excel selects the last cell in the template, even when the cell (or row) is empty.

Any help would be greatly appreciated.

Darin
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Flenley

Board Regular
Joined
Jul 31, 2002
Messages
71
Not sure if this helps, but could run a macro that hides or deletes the blanks rows then prints the range?

This is from the CPearson web site and it will delete blank rows.

Public Sub DeleteBlankRows()

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
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
 

skywaz

New Member
Joined
Sep 6, 2002
Messages
21
Flenley,

Thanks for the quick response. Yes, I have thought of something similar to your post, but I failed to give you a piece of important info that keeps your sub from working. If there are data above and below the blank row, I want to include this row. Basically, I wish to delete all blank rows at the end of the worksheet and continue from bottom upward until I hit a row with data. I hope this makes sense.

Look forward to your response.

Darin
 

Larry_S

New Member
Joined
Sep 25, 2002
Messages
10
Try this.

After the code line that says N=N+1, add 2 lines:

Else
Exit For

The effect of these is that the first line that is not empty (does not qualify for deletion) will cause an exit from the deletion For loop, and from there the termination of the procedure.
 

skywaz

New Member
Joined
Sep 6, 2002
Messages
21

ADVERTISEMENT

ok, here's what I got so far...
The previous code didn't work probably because each blank row contains formulas. The CountA function did not see these rows as blank (I think), so therefore didn't delete them. Can I code so that the CountA function evaluates rows for VALUES only? If no values, then delete. See code below.

Thanks for your help.

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range

'On Error GoTo EndMacro

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
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
'Rows(Rng.Row).Hidden = True 'I woulk like to just hide them
N = N + 1
Else
Exit For
End If
Next R
 

skywaz

New Member
Joined
Sep 6, 2002
Messages
21
I have tried this also and while it works, it's very slow...
With Range("C11:E100")
Set mRange = .Find(What:="", LookIn:=xlValues)
Do While Not mRange Is Nothing
mRange.EntireRow.Hidden = True
Set mRange = .FindNext
Loop
End With

I've tried setting the:
Find(What:="" to...
Find(What:="0" but this didn't work.

Also, this hides all rows, and I only want to hide the rows below the last row containing data.

Thanks again for all your help.
 

skywaz

New Member
Joined
Sep 6, 2002
Messages
21
I have tried this also and while it works, it's very slow...
With Range("C11:E100")
Set mRange = .Find(What:="", LookIn:=xlValues)
Do While Not mRange Is Nothing
mRange.EntireRow.Hidden = True
Set mRange = .FindNext
Loop
End With

I've tried setting the:
Find(What:="" to...
Find(What:="0" but this didn't work.

Also, this hides all rows, and I only want to hide the rows below the last row containing data.

Thanks again for all your help.
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top