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

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.

Quick and crude. This code goes in the applicable Worksheet Code Page. If you need it for more than one sheet, see 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 xlShiftToRight
    Loop
End If
  Next

End With


End Sub

---

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

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

---

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
Attachment 17617

My bad :banghead:

try xlShiftToLeft

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

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

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

Code:

---

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

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.

Attachment 17639

What does Run Time Error 1004 say?

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

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

Attachment 17650

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

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

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:

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.