Deleted empty row for some columns

[Andri]

Please help for the above subject.

the following module only able delete the empty row at Column A only.
Sub DeleteEmptyRow()
Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "A").End(xlUp).Row
For i = cRows To 1 Step -1
If Cells(i, "A").Value = "" Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next
End Sub

But i need to delete a range from Column A to H.

Thank you for your kind attention.

[jtp]

Hi Andri,
This should delete Columns A --> H. I am assuming you want to delete it and push the next row up and not just clear out the selection.

Sub DeleteEmptyRow()
Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "A").End(xlUp).Row
For i = cRows To 1 Step -1
If Cells(i, "A").Value = "" Then
Range("A" & i, "H" & i).Delete shift:=xlUp
End If
Next
End Sub

Hope this helps,

Jason

[Andri]

Dear Jason,

Thank you, it works but some there is one problem again...

This is the situation, my big database at the moment
Name Bonus xxxx up to column N...
Row 1 Ann blank cell
Row 2 Blank cell 50
Row 3 Blank cell 100
Row 4 Blank Row
Row 5 Ben Blank Cell
Row 6 Blank cell 30
Row 7 Blank cell 40
Row 8 Blank Row

if we run the module...it information is not clearly to describe that Ann
has total bonus 150 (100+50).

Brgds,andri

"jtp" wrote:

>
> Hi Andri,
> This should delete Columns A --> H. I am assuming you want to delete
> it and push the next row up and not just clear out the selection.
>
> -Sub DeleteEmptyRow()
> Dim cRows As Long
> Dim i As Long
>
> cRows = Cells(Rows.Count, "A").End(xlUp).Row
> For i = cRows To 1 Step -1
> If Cells(i, "A").Value = "" Then
> Range("A" & i, "H" & i).Delete shift:=xlUp
> End If
> Next
> End Sub-
>
> Hope this helps,
>
> Jason
>
>
> --
> jtp
> ------------------------------------------------------------------------
> jtp's Profile: http://www.excelforum.com/member.php...o&userid=21132
> View this thread: http://www.excelforum.com/showthread...hreadid=495968
>
>

[jtp]

Andri,
Before we continue, let me understand exactly what you want. If you encounter a blank cell in column A, you want to remove that row but if there is a bonus in it (data in column B) you want to add that to the person above them? Is column B the only column that would have data in it if the cell in Column A is empty? I know you said it goes all the way to Column N. If this is the case, you certainly cannot delete columns A thru H for that row otherwise you lose your bonus data.

Also if there is a possibility that the there is Bonus data and Names in Column A then you may not start at the bottom. Your currently looking for the last non-empty row in column A which in your example is Row 5. You would never pick up the bonus in Row 6 & 7. So we will look in Column B.

Well I'll give it a shot. Since I dont know exactly what your worksheet looks like, I will set this up for data in the bonus column only. If there is more that that, I would store them in an array.

Sub DeleteEmptyRow()
Dim cRows As Long
Dim i As Long, bonus As Integer, foundBonus As Boolean

cRows = Cells(Rows.Count, "B").End(xlUp).Row
For i = cRows To 1 Step -1
If Cells(i, "A").Value = "" Then
bonus = bonus + Cells(i, "B").Value
foundBonus = True
Range("A" & i, "H" & i).Delete shift:=xlUp
ElseIf Cells(i, "A").Value <> "" And foundBonus = True Then
Cells(i, "B").Value = Cells(i, "B").Value + bonus
bonus = 0
foundBonus = False
End If

Next
End Sub

Hope this works for you.

Jason


Dear Jason,

Thank you, it works but some there is one problem again...

This is the situation, my big database at the moment
Name Bonus xxxx up to column N...
Row 1 Ann blank cell
Row 2 Blank cell 50
Row 3 Blank cell 100
Row 4 Blank Row
Row 5 Ben Blank Cell
Row 6 Blank cell 30
Row 7 Blank cell 40
Row 8 Blank Row

if we run the module...it information is not clearly to describe that Ann
has total bonus 150 (100+50).

Brgds,andri

[Andri]

Dear Jason,

Thank you for the help and sorry for the confusion. Now i know what i would
like to do...
1. I want to delete "the Blank Row" from column A:N. In this sample only Row
4 and Row 8 will be deleted.
2. Then i want to delete with remark "Blank Cell in column B)...this module
should be automatically only Row 1 and Row 5 AND only from Column B:N
(Excludes Column A).

Brgds,andri

"jtp" wrote:

>
> Andri,
> Before we continue, let me understand exactly what you want. If you
> encounter a blank cell in column A, you want to remove that row but if
> there is a bonus in it (data in column B) you want to add that to the
> person above them? Is column B the only column that would have data in
> it if the cell in Column A is empty? I know you said it goes all the
> way to Column N. If this is the case, you certainly cannot delete
> columns A thru H for that row otherwise you lose your bonus data.
>
> Also if there is a possibility that the there is Bonus data and Names
> in Column A then you may not start at the bottom. Your currently
> looking for the last non-empty row in column A which in your example is
> Row 5. You would never pick up the bonus in Row 6 & 7. So we will look
> in Column B.
>
> Well I'll give it a shot. Since I dont know exactly what your
> worksheet looks like, I will set this up for data in the bonus column
> only. If there is more that that, I would store them in an array.
>
> -Sub DeleteEmptyRow()
> Dim cRows As Long
> Dim i As Long, bonus As Integer, foundBonus As Boolean
>
> cRows = Cells(Rows.Count, "B").End(xlUp).Row
> For i = cRows To 1 Step -1
> If Cells(i, "A").Value = "" Then
> bonus = bonus + Cells(i, "B").Value
> foundBonus = True
> Range("A" & i, "H" & i).Delete shift:=xlUp
> ElseIf Cells(i, "A").Value <> "" And foundBonus = True Then
> Cells(i, "B").Value = Cells(i, "B").Value + bonus
> bonus = 0
> foundBonus = False
> End If
>
> Next
> End Sub-
>
> Hope this works for you.
>
> Jason
>
>
> Dear Jason,
>
> Thank you, it works but some there is one problem again...
>
> This is the situation, my big database at the moment
> Name Bonus xxxx up to column N...
> Row 1 Ann blank cell
> Row 2 Blank cell 50
> Row 3 Blank cell 100
> Row 4 Blank Row
> Row 5 Ben Blank Cell
> Row 6 Blank cell 30
> Row 7 Blank cell 40
> Row 8 Blank Row
>
> if we run the module...it information is not clearly to describe that
> Ann
> has total bonus 150 (100+50).
>
> Brgds,andri
>
>
> --
> jtp
> ------------------------------------------------------------------------
> jtp's Profile: http://www.excelforum.com/member.php...o&userid=21132
> View this thread: http://www.excelforum.com/showthread...hreadid=495968
>
>