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
Error 1004.jpg

[SamT]

My bad

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!!

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!!
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.

Debug Error.JPG

[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

Run time error 1004.jpg

[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.

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

[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.