Removing empty rows issue Please Help!

[James]

Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ws.Range("B" & lngRow) = "" Then
ws.Rows(lngRow).Delete
Else
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)


End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?


Thank you so much for your help.


Regards,


James

 

[michdenis]

Hi,

Try this :

'-----------------------------------
Sub Clean()
Dim ws As Worksheet, LastRow As Long

On Error Resume Next
For Each ws In Worksheets
With ws
.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
LastRow = .Range("B" & .Cells.Rows.Count).End(xlUp).Row
With .Range("D1:F" & LastRow)
.Value = .Value
End With
With .Range("H1:AF" & LastRow)
.Value = .Value
End With
End With
Next
End Sub
'-----------------------------------



"James" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ws.Range("B" & lngRow) = "" Then
ws.Rows(lngRow).Delete
Else
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)


End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?


Thank you so much for your help.


Regards,


James

 

[James]

If I know that the last row will always be B2200, how do I alter the
code? will it run significantly faster?

 

[michdenis]

| will it run significantly faster?

No. Nothing noticeable

But if you want to adapt, you could do this :

'----------------------------------
Sub Clean()
Dim ws As Worksheet

On Error Resume Next
For Each ws In Worksheets
With ws
.Range("B1:B2200").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
With .Range("D1:F2200")
.Value = .Value
End With
With .Range("H1:AF2200")
.Value = .Value
End With
End With
Next
End Sub
'----------------------------------



"James" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
If I know that the last row will always be B2200, how do I alter the
code? will it run significantly faster?