VBA - simplify sheet by removing blanks between non-empty cells... and more...

[Csibész]

Hi,

First and foremost I'm a pre-novice when it comes to code writing so my problem likely be not more than a light finger exercise for all/most of you...
So... I receive xl files at regular intervals where data may be split into more than one cells (not more than 5). The goal is to combine the information in these successive cells into one and to eliminate the empty lines left behind. To my utter surprise my feeble attempt - listed below - almost works: sadly the last instance is ignored.

Sub test()
Dim c, i, k As Integer
k = ActiveSheet.Range("a" & Rows.count).End(xlUp).Row
c = 0
For i = 1 To k
    If IsEmpty(Cells(i, 1)) = True Then
'       cell empty - increment counter
        c = c + 1
    Else
'        MsgBox " Number of empty cells detected   : " & c
'
'       cell non-empty - if first and only non-empty occurence: do nothing, otherwise - do the processing
        If c > 0 Then
            Cells(i - c, 1).Activate
            ActiveCell.Value = ActiveCell.Offset(-1, 1).Value + " " + ActiveCell.Offset(0, 1).Value
            Selection.Cut
            ActiveCell.Offset(-1, 1).Range("A1").Select
            ActiveSheet.Paste
            ActiveCell.Offset(1, -1).Range("A1").Select
            Selection.EntireRow.Delete
            ActiveCell.Offset(-1, 0).Range("A1").Select
            c = 0
        End If
    End If
Next
' debug
'        MsgBox "original last row: " & k & vbNewLine & _
               "current row      : " & i
End Sub

Could someone help me by showing where the problem is and what the solution might be?

For the purpose of illustration a simplified example (columns A&B) and the result after "treatment" (columns C&D) are shown below. Rows 9&10 are causing the grief...

Thanks!

 

[Zot]

Just for note:
When you write Dim c, i, k As Integer, only k will be Integer. The c and i will be Variant
As far as I know, in 64 bit system, Integer will be convert internally to Long. So, might just as well use Long instead.

I have tested your code and if you have more than just a single row blank is column A, the code will not work. It will have blank. Every time you delete a row, you will shift next row into current row count, thus will skip one line in the loop..

I hope this code work to your requirement. I'm assuming that you can never have blank in column B. I will use column B as last row. Note that I just use & as code for Long

Sub test1()

Dim i&

i = 1
Do While Not Len(Cells(i, "B")) = 0
    If Len(Cells(i, "A")) = 0 Then
        If Len(Cells(i, "B")) = 0 Then End
        Cells(i, "B").Offset(-1, 0) = Cells(i, "B").Offset(-1, 0) & " " & Cells(i, "B")
        Cells(i, "A").EntireRow.Delete
        i = i - 1
    Else
        i = i + 1
    End If
Loop

End Sub

 

[Csibész]

I have tested your code and if you have more than just a single row blank is column A, the code will not work. It will have blank. Every time you delete a row, you will shift next row into current row count, thus will skip one line in the loop..

I hope this code work to your requirement. I'm assuming that you can never have blank in column B. I will use column B as last row. Note that I just use & as code for Long

Hello Zot,
Thanks for the solution. Yes, column B will never have blank cells mixed in the data.
To be honest, as I mentioned in the original post, there might be more than a single blank cell somewhere in column A: there could be none, 1, 2, 3, even 5 but not more, in random order... so could eg. a For... ...Next loop above the ELSE statement handle this variability?
Thanks!

 

[Zot]

Hello Zot,
Thanks for the solution. Yes, column B will never have blank cells mixed in the data.
To be honest, as I mentioned in the original post, there might be more than a single blank cell somewhere in column A: there could be none, 1, 2, 3, even 5 but not more, in random order... so could eg. a For... ...Next loop above the ELSE statement handle this variability?
Thanks!

Did not get what you meant. Any loop can handle this.

In you case you use For i= 1 to k. Every time a row is deleted, the k value will still remain the same. Therefore you will keep looping until the original k value even though the data has already been completed. That is why I just use While loop and use column B to check if all date been processed

I have tested your code. Not only the last date 3 in not processed but if you have more than just 1 blank in column A, the result is not what you've wanted I think. You would have blank in column A.

I tested my code will work just fine even for any number of blank row in column A.

 

[Csibész]

Did not get what you meant. Any loop can handle this.

In you case you use For i= 1 to k. Every time a row is deleted, the k value will still remain the same. Therefore you will keep looping until the original k value even though the data has already been completed. That is why I just use While loop and use column B to check if all date been processed

I have tested your code. Not only the last date 3 in not processed but if you have more than just 1 blank in column A, the result is not what you've wanted I think. You would have blank in column A.

I tested my code will work just fine even for any number of blank row in column A.

Oh, I see now! Told you I'm a pre-novice...
Your code works perfectly. Thank you!

 

[Rick Rothstein]

Here is another macro that you can consider which involves less overall looping and less individual row deletions...

Sub Test2()
  Dim Ar As Range
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "B").End(xlUp).Offset(, -1)).SpecialCells(xlBlanks)
    For Each Ar In .Areas
      Ar(1).Offset(-1, 1) = Join(Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Count + 1)))
    Next
    .EntireRow.Delete
  End With
  Application.ScreenUpdating = True
End Sub