Set Print Area VBA

inquiringmind

New Member
Joined
Sep 29, 2006
Messages
3
Hi,

I've searched the web for a couple hours these last couple days for print area macro information, but haven't yet figured out how to do exactly what I need.

The # of columns to print is fixed -- always from A to AJ (although I wouldn't mind just setting this to automatically find the last column of data in case I change the report)

The rows are what's variable -- each row has an indicator in the first column, A B C D or E. Sometimes I'll want to print all rows, sometimes just A B and C, sometimes just AB. If it makes things easier, I'll always be printing a chunk as well as everything above it (i.e. i'll never print just D and E....If I'm pringint D's, I'm printing A through D). So the last letter is just my bottom limit.

I think that just about covers it...sorry if that was a little confusing. In summary, columns are fixed, but rows to print are based on an indicator in the first column.

Any help on how to write the code is HUGELY appreciated!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
try this

Code:
Sub Print_Area()
Dim Rownum As Integer
    Rownum = Range("A1").Value
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AJ$" & Rownum
End Sub
 

inquiringmind

New Member
Joined
Sep 29, 2006
Messages
3
Appreciate the quick response.

However, I'm unclear on where it's learning where to stop printing based on me wanting A, B, C, D, or E to be the last indicator printed. Could you explain that?

EDIT: I forgot to mention that the rows are ordered by this indicator, so all the A's are together, then the B's, etc. That should make things easier.

try this

Code:
Sub Print_Area()
Dim Rownum As Integer
    Rownum = Range("A1").Value
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AJ$" & Rownum
End Sub
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Tried and tested this, although it is case sensitive, only work if you use capitals, but I dont know how to do that and im off home now as its FRIDAY YAY!!!!!

good luck with the code,


Code:
Sub Print_area()
Dim Rownum
Dim a As Integer
Rownum = Sheets("Sheet1").Range("A1").Value
    If Rownum = "A" Then
      a = 1
      Call MacPage(a)
    End If
    If Rownum = "B" Then
      a = 2
      Call MacPage(a)
    End If
    If Rownum = "C" Then
      a = 3
      Call MacPage(a)
    End If
    If Rownum = "D" Then
      a = 4
      Call MacPage(a)
    End If
    If Rownum = "E" Then
      a = 5
      Call MacPage(a)
    End If
End Sub
and this will need to be in the same module
Code:
Sub MacPage(a As Integer)
  ActiveSheet.PageSetup.PrintArea = "$A$1:$AJ$" & a
End Sub

EDIT: sorry, didnt see your edit, so basically there are multiple rows for each letter, so if it isnt solved on monday ill see what i can do.

Jamie
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Here you go, this should work as you want it too,

Code:
Sub Print_Area()
    On Error GoTo Err
    Dim myrange As Range
    Dim x As Integer
    Dim rng As Range
    Set rng = ActiveSheet.Range("A1:A65536")
    numA = Application.WorksheetFunction.CountIf(rng, "A")
    numB = Application.WorksheetFunction.CountIf(rng, "B")
    numC = Application.WorksheetFunction.CountIf(rng, "C")
    numD = Application.WorksheetFunction.CountIf(rng, "D")
    numE = Application.WorksheetFunction.CountIf(rng, "E")
    Set myrange = ActiveSheet.Range("A1")
    If myrange = "A" Then
    x = numA
    End If
    If myrange = "B" Then
    x = numA + numB
    End If
    If myrange = "C" Then
    x = numA + numB + numC
    End If
    If myrange = "D" Then
    x = numA + numB + numC + numD
    End If
    If myrange = "E" Then
    x = numA + numB + numC + numD + numE
    End If
ActiveSheet.PageSetup.PrintArea = ("A1:AJ") & x
GoTo Fin
Err:
MsgBox "Please use a Capital letter in A1"
Fin:
End Sub

Remember you must use CAPITALS with this code, but there is a fail safe if lowercase is entered accidentally.
 

inquiringmind

New Member
Joined
Sep 29, 2006
Messages
3
Awesome!

Thanks so much for th ehelp. Only issue was that the macro was cutting everything 1 row short so I had to add "+1" to all of the x equations to get all the data.

The help was much appreciated!
 

Forum statistics

Threads
1,136,502
Messages
5,676,225
Members
419,614
Latest member
bfletcher

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