Deleted empty row for some columns
 

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.

Deleted empty row for some columns
 

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

Deleted empty row for some columns
 

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

Deleted empty row for some columns
 

Andri,
Before we continue, let me understand exactly what you want. If yo
encounter a blank cell in column A, you want to remove that row but i
there is a bonus in it (data in column B) you want to add that to th
person above them? Is column B the only column that would have data i
it if the cell in Column A is empty? I know you said it goes all th
way to Column N. If this is the case, you certainly cannot delet
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 Name
in Column A then you may not start at the bottom. Your currentl
looking for the last non-empty row in column A which in your example i
Row 5. You would never pick up the bonus in Row 6 & 7. So we will loo
in Column B.

Well I'll give it a shot. Since I dont know exactly what you
worksheet looks like, I will set this up for data in the bonus colum
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 tha
Ann
has total bonus 150 (100+50).

Brgds,andr

--
jt
-----------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...fo&userid=2113
View this thread: http://www.excelforum.com/showthread.php?threadid=49596

Deleted empty row for some columns
 

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