| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Empty Rows
Hi all
My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
'Delete Empty Rows
Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
Tom,
This code works perfectly, Thanks. But why did my code not worked? Can you explain to me please. Thank you very much V_ Tom Ogilvy wrote: 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
You were looping down the page, consequently if you deleted a row the
counter acts on the next row, even though a row had been deleted! By reversing this and looping up the page deleted rows were never going to affect the counter as they had already been passed over. -- Cheers Nigel "nxqviet" wrote in message oups.com... Tom, This code works perfectly, Thanks. But why did my code not worked? Can you explain to me please. Thank you very much V_ Tom Ogilvy wrote: 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
Row Number Column A Value
1 Dum 2 <blank 3 <blank 4 Dum now contentcount is 2 and row 2 is deleted. Results Original Row Numbe New Row Number Column A Value 1 1 Dum 3 2 <blank 4 3 Dum but the loop completes and contentcount now equals 3 and it looks at original row number 4 (now row number 3) - original row number 3 (now row number 2) is skipped in the new loop. -- Regards, Tom Ogilvy "nxqviet" wrote: Tom, This code works perfectly, Thanks. But why did my code not worked? Can you explain to me please. Thank you very much V_ Tom Ogilvy wrote: 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
Ahhhh, I can see it now. This is interesting =) . Thanks a lot.
I have a follow up question, If I want to close the current file by inserting a .Quit Code at the end of the Code, And I want it to close Without Saving. How do I do that? Lets say the file name is Export.xls. Thanks, V_ Tom Ogilvy wrote: Row Number Column A Value 1 Dum 2 <blank 3 <blank 4 Dum now contentcount is 2 and row 2 is deleted. Results Original Row Numbe New Row Number Column A Value 1 1 Dum 3 2 <blank 4 3 Dum but the loop completes and contentcount now equals 3 and it looks at original row number 4 (now row number 3) - original row number 3 (now row number 2) is skipped in the new loop. -- Regards, Tom Ogilvy "nxqviet" wrote: Tom, This code works perfectly, Thanks. But why did my code not worked? Can you explain to me please. Thank you very much V_ Tom Ogilvy wrote: 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
workbooks("Export.xls").Close SaveChanges:=False
-- Regards, Tom Ogilvy "nxqviet" wrote: Ahhhh, I can see it now. This is interesting =) . Thanks a lot. I have a follow up question, If I want to close the current file by inserting a .Quit Code at the end of the Code, And I want it to close Without Saving. How do I do that? Lets say the file name is Export.xls. Thanks, V_ Tom Ogilvy wrote: Row Number Column A Value 1 Dum 2 <blank 3 <blank 4 Dum now contentcount is 2 and row 2 is deleted. Results Original Row Numbe New Row Number Column A Value 1 1 Dum 3 2 <blank 4 3 Dum but the loop completes and contentcount now equals 3 and it looks at original row number 4 (now row number 3) - original row number 3 (now row number 2) is skipped in the new loop. -- Regards, Tom Ogilvy "nxqviet" wrote: Tom, This code works perfectly, Thanks. But why did my code not worked? Can you explain to me please. Thank you very much V_ Tom Ogilvy wrote: 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Empty Rows
Thanks Tom
Tom Ogilvy wrote: workbooks("Export.xls").Close SaveChanges:=False -- Regards, Tom Ogilvy "nxqviet" wrote: Ahhhh, I can see it now. This is interesting =) . Thanks a lot. I have a follow up question, If I want to close the current file by inserting a .Quit Code at the end of the Code, And I want it to close Without Saving. How do I do that? Lets say the file name is Export.xls. Thanks, V_ Tom Ogilvy wrote: Row Number Column A Value 1 Dum 2 <blank 3 <blank 4 Dum now contentcount is 2 and row 2 is deleted. Results Original Row Numbe New Row Number Column A Value 1 1 Dum 3 2 <blank 4 3 Dum but the loop completes and contentcount now equals 3 and it looks at original row number 4 (now row number 3) - original row number 3 (now row number 2) is skipped in the new loop. -- Regards, Tom Ogilvy "nxqviet" wrote: Tom, This code works perfectly, Thanks. But why did my code not worked? Can you explain to me please. Thank you very much V_ Tom Ogilvy wrote: 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = ContentCount to 1 step -1 If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u -- Regards, Tom Ogilvy "nxqviet" wrote: Hi all My question is about Macro that delete empty row in a data range. here is the code that I have. 'Delete Empty Rows Dim ContentCount As Integer Dim u As Integer ContentCount = Range(("PContent" & i)).Rows.Count For u = 1 To ContentCount If Range("PContent" & i).Cells(u, 1).Value = "" Then Range("PContent" & i).Rows(u).Delete End If Next u This is a part of a much larger code. On this sheet, I have many ranges with the name of PContent1, PContent2,.... and "i" will be the count of the number of Range that I'll use. And "u" is the count of the number of Rows in every "i" Range. This Code will look through the ranges that I used and Delete all the empty Rows. The code seem to work fine, except it Deletes only a few number of empty row in every given range, then move on to the next range. Please help, Thanks a lot, V_ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
| Delete Empty Rows | Excel Discussion (Misc queries) | |||
| How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
| Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
| Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
Linear Mode
