Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
I have a work sheet with 65,536 rows
only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
Wait, are you saying you want only the 12,000 rows showing data to show and
delete the remaining rows? Or is there formulas or something that you are trying to get rid of below the 12,000 rows? Because I don't think it's possible to actually delete the blank rows. You can hide them. "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
Alternatively (from what AKphidelt wrote), if you have had data in those
high-numbered rows, take at look at this page: http://www.contextures.com/xlfaqApp.html#Unused But if they've never actually been part of the 'UsedRange', then hiding them is pretty much the only solution. "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
hi,
I may be misunderstanding but as i read your post, you want to delete the empty rows below your data?!?!? not possible. excel is fixed at 255 columns wide and 65536 rows high. and that is etched in stone by the excel gods at microsoft. you can hide them so that you can't see them but they will always be there. Sorry. to hide rows.... Select the first row below your data. shift+end+down. formatrowhide to unhide rows..... select the sheet. formatrowunhide regards FSt1 "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
The spread sheet was downloaded from a website using a non-Excel
application. I'm not sure who did the conversion, but i think he just checked a box that asked for the format. What does hiding the rows mean? Thanks, Peter "AKphidelt" wrote in message ... Wait, are you saying you want only the 12,000 rows showing data to show and delete the remaining rows? Or is there formulas or something that you are trying to get rid of below the 12,000 rows? Because I don't think it's possible to actually delete the blank rows. You can hide them. "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
Okay, so I just want to hide the rows;
but I want to do it in a macro using 2002 VBA. Peter. "FSt1" wrote in message ... hi, I may be misunderstanding but as i read your post, you want to delete the empty rows below your data?!?!? not possible. excel is fixed at 255 columns wide and 65536 rows high. and that is etched in stone by the excel gods at microsoft. you can hide them so that you can't see them but they will always be there. Sorry. to hide rows.... Select the first row below your data. shift+end+down. formatrowhide to unhide rows..... select the sheet. formatrowunhide regards FSt1 "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
Try something like this, not sure if it will read the cells correctly or
not... but try Range("A1").End(xlDown).Offset(1,0).Activate Range(ActiveCell,ActiveCell.End(xlDown)).EntireRow .Hide Im not next to excel right now, so this is just off the top of my head. Someone else probably has something better. "Peter Chatterton" wrote: Okay, so I just want to hide the rows; but I want to do it in a macro using 2002 VBA. Peter. "FSt1" wrote in message ... hi, I may be misunderstanding but as i read your post, you want to delete the empty rows below your data?!?!? not possible. excel is fixed at 255 columns wide and 65536 rows high. and that is etched in stone by the excel gods at microsoft. you can hide them so that you can't see them but they will always be there. Sorry. to hide rows.... Select the first row below your data. shift+end+down. formatrowhide to unhide rows..... select the sheet. formatrowunhide regards FSt1 "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
Will you please explain what is wrong with Delete?
It's what I do all the time manually, and it shows a reduced row count. For some reason or other I can't duplicate it right now, but maybe that's just Excel. Peter. "FSt1" wrote in message ... hi, I may be misunderstanding but as i read your post, you want to delete the empty rows below your data?!?!? not possible. excel is fixed at 255 columns wide and 65536 rows high. and that is etched in stone by the excel gods at microsoft. you can hide them so that you can't see them but they will always be there. Sorry. to hide rows.... Select the first row below your data. shift+end+down. formatrowhide to unhide rows..... select the sheet. formatrowunhide regards FSt1 "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't delete empty but active cells
Thanks for the suggestion AK, but Excel claims that the Hide metod
is something that only applies to UserForms and that seems to be supported when i try and run it. Peter "AKphidelt" wrote in message ... Try something like this, not sure if it will read the cells correctly or not... but try Range("A1").End(xlDown).Offset(1,0).Activate Range(ActiveCell,ActiveCell.End(xlDown)).EntireRow .Hide Im not next to excel right now, so this is just off the top of my head. Someone else probably has something better. "Peter Chatterton" wrote: Okay, so I just want to hide the rows; but I want to do it in a macro using 2002 VBA. Peter. "FSt1" wrote in message ... hi, I may be misunderstanding but as i read your post, you want to delete the empty rows below your data?!?!? not possible. excel is fixed at 255 columns wide and 65536 rows high. and that is etched in stone by the excel gods at microsoft. you can hide them so that you can't see them but they will always be there. Sorry. to hide rows.... Select the first row below your data. shift+end+down. formatrowhide to unhide rows..... select the sheet. formatrowunhide regards FSt1 "Peter Chatterton" wrote: I have a work sheet with 65,536 rows only the first 12,000 of which have data, but I can't figure out how to delete the empty but active rows. I could do a reverse Find from the end and then delete the trailing Range, but hoped there might be an easier way. I thought the following, from Chapter 5 of Excel 2002 VBA Programmer's Reference, (http://www.wrox.com/WileyCDA/WroxTit...load_code.html) might work (going by the title), but it doesn't. Sub DeleteEmptyRows() Dim rngRow As Range For Each rngRow In ActiveSheet.UsedRange.Rows If WorksheetFunction.CountA(rngRow) = 0 Then rngRow.EntireRow.Delete End If Next rngRow End Sub Hope you can help, Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete row if 2 cells are empty | Excel Discussion (Misc queries) | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
Delete cells not in Active Worksheet | Excel Programming | |||
Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
delete all EMPTY cells along each COLUMN | Excel Programming |