Identify and Delete all Rows and Columns NOT in Print Area

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
 
C

Cliff Myers

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
 
K

Keith Young

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
 
T

Tom Ogilvy

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.
 
K

Keith Young

Perfect!!! And not even 1 typo.

Thanks so much Tom for sharing your knowledge and experience with this group
over the years.
 
D

Dana DeLouis

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
 
K

Keith Young

Dana, thanks for the additional input. This seems to work just as well and
gives me some additional ideas to work with.
 
Top