| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing blank rows
I have a 1-column spreadsheet that contains data in column H (with a heading
in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Always start on the bottom and go up when you try to delete rows Bob
See http://www.rondebruin.nl/delete.htm -- Regards Ron De Bruin http://www.rondebruin.nl "Bob" wrote in message ... I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Ron,
Thanks for your help! I sincerely appreciate it. Unfortunately, I am a novice programmer, and after reviewing the info on your site, I was overwhelmed. To be candid, I was hoping for someone to simply modify the code below so it would work the first time (rather than having to run it twice). Regards, Bob "Ron de Bruin" wrote: Always start on the bottom and go up when you try to delete rows Bob See http://www.rondebruin.nl/delete.htm -- Regards Ron De Bruin http://www.rondebruin.nl "Bob" wrote in message ... I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Grüezi Bob
Bob schrieb am 20.06.2006 I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. I would greatly appreciate any help. Try the following code: With Range("H:H") .Replace " ", "", xlWhole .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With Mit freundlichen Grüssen Thomas Ramel -- - MVP für Microsoft-Excel - [Win XP Pro SP-2 / xl2000 SP-3] Microsoft Excel - Die ExpertenTipps: (http://tinyurl.com/9ov3l und http://tinyurl.com/cmned) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Thomas,
Very slick! I gave your macro a try and was amazed at how fast it works. In fact, it even caught cells with 2 blank spaces. Thank you! I sincerely appreciate all your help. Regards, Bob "Thomas Ramel" wrote: Grüezi Bob Bob schrieb am 20.06.2006 I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. I would greatly appreciate any help. Try the following code: With Range("H:H") .Replace " ", "", xlWhole .SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With Mit freundlichen Grüssen Thomas Ramel -- - MVP für Microsoft-Excel - [Win XP Pro SP-2 / xl2000 SP-3] Microsoft Excel - Die ExpertenTipps: (http://tinyurl.com/9ov3l und http://tinyurl.com/cmned) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
i had a similar situation and applied the following logic:
With Range("h2:h1000") 'row i am deleting index = ''''''' ..Cells(index + 1, 1).ClearContents 'bubble up the rest of the materials For last = 1 To 998 If .Cells(last , 1) = "" or .cells(last,1)=" " Then For r = last To 998 If .Cells(r + 1, 1) < "" Then .Cells(last + 1, 1) = .Cells(r + 1, 1) .Cells(r + 1, 1).ClearContents Exit For End If Next r End If Next last End With i also would be interested if someone else had a more efficient way of doing this (recusrisve function?) Bob wrote: I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Mark,
Thanks for your help! Check out Thomas Ramel's solution in the thread above. It is extremely concise. I tried it out on my data and was amazed at how fast it worked! I'm going to have to study his code closely to understand how it works. Regards, Bob " wrote: i had a similar situation and applied the following logic: With Range("h2:h1000") 'row i am deleting index = ''''''' ..Cells(index + 1, 1).ClearContents 'bubble up the rest of the materials For last = 1 To 998 If .Cells(last , 1) = "" or .cells(last,1)=" " Then For r = last To 998 If .Cells(r + 1, 1) < "" Then .Cells(last + 1, 1) = .Cells(r + 1, 1) .Cells(r + 1, 1).ClearContents Exit For End If Next r End If Next last End With i also would be interested if someone else had a more efficient way of doing this (recusrisve function?) Bob wrote: I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Hi Bob
Be aware that SpecialCells errors when there are no blanks Use a on error Also there is a limit See http://www.rondebruin.nl/specialcells.htm -- Regards Ron De Bruin http://www.rondebruin.nl "Bob" wrote in message ... Mark, Thanks for your help! Check out Thomas Ramel's solution in the thread above. It is extremely concise. I tried it out on my data and was amazed at how fast it worked! I'm going to have to study his code closely to understand how it works. Regards, Bob " wrote: i had a similar situation and applied the following logic: With Range("h2:h1000") 'row i am deleting index = ''''''' ..Cells(index + 1, 1).ClearContents 'bubble up the rest of the materials For last = 1 To 998 If .Cells(last , 1) = "" or .cells(last,1)=" " Then For r = last To 998 If .Cells(r + 1, 1) < "" Then .Cells(last + 1, 1) = .Cells(r + 1, 1) .Cells(r + 1, 1).ClearContents Exit For End If Next r End If Next last End With i also would be interested if someone else had a more efficient way of doing this (recusrisve function?) Bob wrote: I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Ron,
Thanks for the heads-up. Fortunately, the range of my data never exceeds more than a 1,000 rows, and it's only 1-column wide. With respect to adding an OnError check, I can certainly do that, but my data always has blank cells. So do I really need to add the check? If so, where in Ramel's code would I do so? Thanks for your help. Regards, Bob "Ron de Bruin" wrote: Hi Bob Be aware that SpecialCells errors when there are no blanks Use a on error Also there is a limit See http://www.rondebruin.nl/specialcells.htm -- Regards Ron De Bruin http://www.rondebruin.nl "Bob" wrote in message ... Mark, Thanks for your help! Check out Thomas Ramel's solution in the thread above. It is extremely concise. I tried it out on my data and was amazed at how fast it worked! I'm going to have to study his code closely to understand how it works. Regards, Bob " wrote: i had a similar situation and applied the following logic: With Range("h2:h1000") 'row i am deleting index = ''''''' ..Cells(index + 1, 1).ClearContents 'bubble up the rest of the materials For last = 1 To 998 If .Cells(last , 1) = "" or .cells(last,1)=" " Then For r = last To 998 If .Cells(r + 1, 1) < "" Then .Cells(last + 1, 1) = .Cells(r + 1, 1) .Cells(r + 1, 1).ClearContents Exit For End If Next r End If Next last End With i also would be interested if someone else had a more efficient way of doing this (recusrisve function?) Bob wrote: I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing blank rows
Hi Bob
With Range("H:H") .Replace " ", "", xlWhole On Error Resume Next 'In case there are no blank cells .SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 End With -- Regards Ron De Bruin http://www.rondebruin.nl "Bob" wrote in message ... Ron, Thanks for the heads-up. Fortunately, the range of my data never exceeds more than a 1,000 rows, and it's only 1-column wide. With respect to adding an OnError check, I can certainly do that, but my data always has blank cells. So do I really need to add the check? If so, where in Ramel's code would I do so? Thanks for your help. Regards, Bob "Ron de Bruin" wrote: Hi Bob Be aware that SpecialCells errors when there are no blanks Use a on error Also there is a limit See http://www.rondebruin.nl/specialcells.htm -- Regards Ron De Bruin http://www.rondebruin.nl "Bob" wrote in message ... Mark, Thanks for your help! Check out Thomas Ramel's solution in the thread above. It is extremely concise. I tried it out on my data and was amazed at how fast it worked! I'm going to have to study his code closely to understand how it works. Regards, Bob " wrote: i had a similar situation and applied the following logic: With Range("h2:h1000") 'row i am deleting index = ''''''' ..Cells(index + 1, 1).ClearContents 'bubble up the rest of the materials For last = 1 To 998 If .Cells(last , 1) = "" or .cells(last,1)=" " Then For r = last To 998 If .Cells(r + 1, 1) < "" Then .Cells(last + 1, 1) = .Cells(r + 1, 1) .Cells(r + 1, 1).ClearContents Exit For End If Next r End If Next last End With i also would be interested if someone else had a more efficient way of doing this (recusrisve function?) Bob wrote: I have a 1-column spreadsheet that contains data in column H (with a heading in cell H1). Some rows in column H contain no data or a single space. Starting with cell H2, I needed a macro that would examine each cell in column H and automatically delete a row where no data or only 1 space exists. The macro would terminate after reaching row 1,000. Someone else in this forum proposed the following macro: Set currentCell = Worksheets("Sheet1").Range("H2") for each cell in range("H2:H1000") Set nextCell = currentCell.Offset(1, 0) If Len(currentCell.Value) <= 1 Then currentCell.EntireRow.Delete End If Set currentCell = nextCell Next Unfortunately, when I run the macro the first time, SOME rows with cells whose LEN<=1 still remains! When I run the macro a second time, it then catches and removes those remaining rows. Does anyone have any idea why I need to run the aforementioned macro twice for it to truly complete the job? I would greatly appreciate any help. Thanks. Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing Blank Rows ? | New Users to Excel | |||
| Removing blank rows | Excel Programming | |||
| Removing blank rows in a worksheet | Excel Worksheet Functions | |||
| Copying and pasting a worksheet to a blank and removing blank rows | Excel Programming | |||
| Removing Blank Rows? | Excel Programming | |||
Hybrid Mode
