Identify and Delete all Rows and Columns NOT in Print Area

  • Thread starter Thread starter Keith Young
  • Start date Start date
K

Keith Young

Basically , I am trying to figure a way to identify and delete entire rows
and entire columns that do not intersct with the print area on a given
sheet.

For example, the print_area on Sheet1 is set to E3:J36. I would need code
to delete Rows 1 and 2 and also Rows 37 through 65536. In addition, Columns
A,B,C,D and Columns K through IV need to be deleted.

Is there a way to do this using the Intersect, or any other method?

Thanks in advance. Using Excel 2002.

Keith
 
With you saying which rows and columns need to be deleted it seems you've
already identified them. Just click rows 1 & 2, right click, click delete,
now your data becomes E1:J34, now click columns A,B,C, & D, again right
click, click delete, now your data becomes A1:F34.

As long as you are not using a border on the cells after column F and row 34
then nothing will print if no data exists.

HTH
 
Sorry - I guess I was not clear enough. The rows and columns that need to
be deleted will need to be identified using VBA. In addition the Print_Area
will need to be identified with code. In the end this code will need to be
run on many sheets within a workbook, each of which has a specific (and
different) Print_Area already set.

Any ideas on how to do this?

Thanks for your help
 
Dim pa as Range
Dim rngrow as Range
Dim lastRow as long, lastCol as Long
Dim i as Long
set pa = Activesheet.names("Print_Area").RefersToRange
set rngrow = activesheet.UsedRange
lastRow = rngrow.Rows(rngrow.rows.count).Row
lastCol = rngRow.Columns(rngrow.Columns.count).Column
for i = lastRow to 1 step -1
if intersect(pa,cells(i,1).Entirerow) is nothing then
rows(i).Entirerow.Delete
end if
Next
for i = lastCol to 1 step -1
if intersect(pa,cells(1,i).EntireColumn) is nothing then
columns(i).EntireColumn.Delete
end if
Next


Above is untested and may contain typos, but should represent a workable
approach.
 
Perfect!!! And not even 1 typo.

Thanks so much Tom for sharing your knowledge and experience with this group
over the years.
 
Perhaps just another idea to play with:

Sub Demo()
Dim nr, nc, sr, sc

With Range(ActiveSheet.PageSetup.PrintArea)
nr = .Rows.Count
nc = .Columns.Count
sr = .Rows(1).Row
sc = .Columns(1).Column
End With

On Error Resume Next
Columns(1).Resize(1, sc - 1).EntireColumn.Delete
Rows(1).Resize(sr - 1).Delete

Range(Cells(nr + 1, 1), Cells(Rows.Count, Columns.Count)).Delete
Range(Cells(1, nc + 1), Cells(Rows.Count, Columns.Count)).Delete
ActiveSheet.UsedRange
End Sub
 
Dana, thanks for the additional input. This seems to work just as well and
gives me some additional ideas to work with.
 
Back
Top