Identify and Delete all Rows and Columns NOT in Print Area
 

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

Identify and Delete all Rows and Columns NOT in Print Area
 

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

"Keith Young" wrote in message
...
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

Identify and Delete all Rows and Columns NOT in Print Area
 

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


"Cliff Myers" wrote in message
...
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

"Keith Young" wrote in message
...
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

Identify and Delete all Rows and Columns NOT in Print Area
 

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.

--
Regards,
Tom Ogilvy



"Keith Young" wrote in message
...
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


"Cliff Myers" wrote in message
...
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

"Keith Young" wrote in message
...
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

Identify and Delete all Rows and Columns NOT in Print Area
 

Perfect!!! And not even 1 typo.

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Keith Young" wrote in message
...
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


"Cliff Myers" wrote in message
...
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

"Keith Young" wrote in message
...
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

Identify and Delete all Rows and Columns NOT in Print Area
 

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 DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Keith Young" wrote in message
...
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


"Cliff Myers" wrote in message
...
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

"Keith Young" wrote in message
...
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

Identify and Delete all Rows and Columns NOT in Print Area
 

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

"Dana DeLouis" wrote in message
...
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 DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Keith Young" wrote in message
...
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


"Cliff Myers" wrote in message
...
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

"Keith Young" wrote in message
...
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