Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I identify matching rows of data and delete them both? | Excel Discussion (Misc queries) | |||
Using named range to extend print area for variable number of columns | Excel Worksheet Functions | |||
delete unused columns and grey out the rest area | Excel Worksheet Functions | |||
print area selection slows excel, formula for hiding rows?? | Excel Discussion (Misc queries) | |||
Can you delete all hidden columns in an area in one move? | Excel Discussion (Misc queries) |