Align Row Content with cell format to Left column & Delete Blank row

[JOEYSCLEE]

Hi, there
I have 100Rows plus in 1 worksheet. Would you please help to state macro to Align Row Content to Left Column (including Cell format & color) and delete the blank row as well?

P.S. Enclosed the Excel file for your reviewing. The worksheet - sheet1 is the original data & the Result tab is what I want.

[SamT]

Quick and crude. This code goes in the applicable Worksheet Code Page. If you need it for more than one sheet, see below.

Option Explicit

Sub VBAX_AlignToLeft()
Dim LastRow As Long
Dim i As Long

LastRow = InputBox("What is the last Row number to align?", "Select Row Number")

With Range("A:A")
For i = 1 To LastRow
If WorksheetFunction.CountA(Rows(i)) > 0 Then
Do While .Cells(i).Value = ""
.Cells(i).Delete xlShiftToRight
Loop
End If
Next

End With


End Sub

This code is useable on any sheet. It goes in a Standard Module


Option Explicit

Sub VBAX_AlignToLeft()
Dim LastRow As Long
Dim i As Long

LastRow = InputBox("What is the last Row number to align?", "Select Row Number")

With ActiveSheet

With Range("A:A")
For i = 1 To LastRow
If WorksheetFunction.CountA(Rows(i)) > 0 Then
Do While .Cells(i).Value = ""
.Cells(i).Delete xlShiftToRight
Loop
End If
Next

End With
End With


End Sub

[JOEYSCLEE]

Thanks for the reply! After running both codes, they don't work and showed Run-time error'1004 & Delete method of Range class failed. Meanwhile, .Cells(i).Delete xlShiftToRight is highlighted after debug
17617

[SamT]

My bad :banghead:

try xlShiftToLeft

Shift right is not an option when deleting cells. It's either shift up or shift left.

[JOEYSCLEE]

Thank you for the quick response!!:bow:

The following Macro is working for Aligning Row Content To Left. However, could you please to add the line to delete BLANK Row with the below code?


Option Explicit

Sub VBAX_AlignToLeft()
Dim LastRow As Long
Dim i As Long

LastRow = InputBox("What is the last Row number to align?", "Select Row Number")


With Range("A:A")
For i = 1 To LastRow
If WorksheetFunction.CountA(Rows(i)) > 0 Then
Do While .Cells(i).Value = ""
.Cells(i).Delete (xlShiftToLeft)
Loop
End If
Next

End With

End Sub

[SamT]

Remember, this version must be in the applicable Worksheet's Code page. You can make the same change to the other version.


Option Explicit

Sub VBAX_AlignToLeft()
Dim LastRow As Long
Dim i As Long

LastRow = InputBox("What is the last Row number to align?", "Last Row Number")


With Range("A:A")
For i = LastRow to i Step -1
If WorksheetFunction.CountA(Rows(i)) > 0 Then
Do While .Cells(i).Value = ""
.Cells(i).Delete (xlShiftToLeft)
Loop
Else
Rows(i). Delete
End If
Next
End With

End Sub

When Deleting Rows, always work from the bottom up

[JOEYSCLEE]

Thank you for the comment!!:cleverman
The Revised Code is workable But it showed Run-time error'1004' after running the macro. The following line is "If WorksheetFunction.CountA(Rows(i)) > 0 Then" is highlighted.

17639

[SamT]

What does Run Time Error 1004 say?

Please use the Go Advanced page to upload some sample data.

[JOEYSCLEE]

The Error messaged is Application-defined or object-defined error

17650

[SamT]

In the highlighted line, change "Rows" to "ActiveSheet.Rows"

[p45cal]

For i = LastRow To i Step -1
?!
For i = LastRow To 1 Step -1
surely.

[JOEYSCLEE]

Thanks p45cal!! There is no more error code after running the macro. :yay

Also, Thanks SamT...You helped me a lot with your advised code.:jsmile:

[SamT]

Surely, 1ndeed.

That's a real bad habit my keyboard has. I want "i" and I get "1". I want "1" and I get "!". Sometimes when I want "i", I even get "!". But never the reverse.

What can I say Dude, it's a Dell.