Solved: How To Delete Column O/empty columns from all sheets in a work book?

[alu]

Hi, Lucas gave me this great bit of Code (EDIT: It Makes all sheets fit to 1 page wide and infinite tall & sets up the margins):

Option Explicit
Sub SetupAllSheets()
'
Dim M As Long, N As Long, Firstsht As Long, Lastsht As Long, Sheet As Object
'
Lastsht = Sheets.Count
M = 0: N = Lastsht
'
For Each Sheet In Sheets
If Not Sheet.Visible Then N = N - 1
If Sheet.Visible And Sheet.Type = xlWorksheet Then
If WorksheetFunction.CountA(Sheet.UsedRange) = 0 Then
N = N - 1
End If
End If
Next
'
For Firstsht = 1 To Lastsht
'
If Sheets(Firstsht).Visible = True Then
'
If Not TypeName(Sheets(Firstsht)) = "Chart" Then
If WorksheetFunction.CountA(Sheets(Firstsht).UsedRange) <> 0 Then
M = M + 1
GoSub DoPrint
End If
Else 'else it's a chart
M = M + 1
GoSub DoPrint
End If
'
End If
'
Next 'Firstsht
Exit Sub
DoPrint:
With Sheets(Firstsht).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.HeaderMargin = Application.InchesToPoints(0.196850393700787)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
With Application
.EnableEvents = False
' Sheets(Firstsht).PrintPreview '.PrintOut
.EnableEvents = True
End With
Return
End Sub


The thing is my data only uses column A-N and column O is still there, if I delete or hide column O, column P appears etc... any ideas? thanks all...

[lucas]

Try this one Alu
Sub SetupAllSheets()
'
Dim M As Long, N As Long, Firstsht As Long, Lastsht As Long, Sheet As Object
'
Lastsht = Sheets.Count
M = 0: N = Lastsht
'
For Each Sheet In Sheets
If Not Sheet.Visible Then N = N - 1
If Sheet.Visible And Sheet.Type = xlWorksheet Then
If WorksheetFunction.CountA(Sheet.UsedRange) = 0 Then
N = N - 1
End If
End If
Next
'
For Firstsht = 1 To Lastsht
'
If Sheets(Firstsht).Visible = True Then
'
If Not TypeName(Sheets(Firstsht)) = "Chart" Then
If WorksheetFunction.CountA(Sheets(Firstsht).UsedRange) <> 0 Then
M = M '+ 1
GoSub DoPrint
End If
Else 'else it's a chart
M = M + 1
GoSub DoPrint
End If
'
End If
'
Next 'Firstsht
Exit Sub
DoPrint:
With Sheets(Firstsht).PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.HeaderMargin = Application.InchesToPoints(0.196850393700787)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
With Application
.EnableEvents = False
' Sheets(Firstsht).PrintPreview '.PrintOut
.EnableEvents = True
End With
Return
End Sub

[alu]

Unfortunatly that didn't work. This line does do the trick:
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$6"

But the last cell N6 will vary on each sheet and each time the report is run. i.e the above function runs today but tomorrow I would need:

ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"

thanks for the help all, Im totally new to this and its a baptism of fire, but im learning!!

[geekgirlau]

What about this (assuming that N is always the last column):

LastRow = Sheets(Firstsht).UsedRange.Rows.Count

ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & LastRow

[alu]

Thanks geekgirlau, I used your original code:

What about this (assuming that N is always the last column):

LastRow = Sheets(Firstsht).UsedRange.Rows.Count

ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & LastRow

and chopped it up a bit:
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$6"
Sheets("5").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$115"
Sheets("4").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$7"
Sheets("3").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$31"
Sheets("2").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$13"
Sheets("1").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$35"

The problem I forsee is next month when the N values change to N66, N83 etc...

But I'll worry about that next month!! Thank you!!

[geekgirlau]

The idea is that the code I sent you originally will calculate the last row, and therefore automatically adjust each month. Why don't you try adding it just after the Page Setup section of your code, and see where it sets the print area, then add some more rows and test again.