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_

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_

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_

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_

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_

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_

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_

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_