Set Print Area VBA

[inquiringmind]

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!

 

[JamieDuncan]

try this

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

 

[inquiringmind]

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

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

 

[JamieDuncan]

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,

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

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]

Here you go, this should work as you want it too,

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]

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!