CODEWITHSUNDEEP
excelvba
Sreeharsha Kasturi
Sreeharsha Kasturi

Reputation: 155

how to merge consecutive empty cells in excel

I have an excel file with something like below data Original data

enter image description here I want to merge all empty cells (automatically) which appears before it, like below after merging should look like this enter image description here

As there are many files, can't do it manually so looking for way to do using script or something else. Thought of doing using autoit but couldn't. Please help :)

have not done vba code anytime but could reach till below the help of google

Sub abc()
Dim a As Integer
Dim b As Integer
Dim c As Integer
b=1
For a = b To 308
If Cells(a, c) = "" Then
Range(Cells(a, c), Cells(a - 1, c)).Merge
End If
Next
End Sub

Upvotes: 0

Views: 3395

Answers (2)

Saurav Sarma
Saurav Sarma

Reputation: 1

Sub mergeBlankRows()
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim nRow As Integer
    Dim nCol As Integer
    nRow = ActiveSheet.UsedRange.Rows.Count
    nCol = ActiveSheet.UsedRange.Columns.Count
    For c = 1 To nCol
    b = 3
    For a = b - 1 To nRow
    If Cells(a, c) = "" Then
    Range(Cells(a, c), Cells(a - 1, c)).Merge
    Range(Cells(a, c), Cells(a - 1, c)).HorizontalAlignment = xlCenter
    Range(Cells(a, c), Cells(a - 1, c)).VerticalAlignment = xlCenter
    Else
    b = a
    End If
    Next
    Next
End Sub

nRow = ActiveSheet.UsedRange.Rows.Count and nCol = ActiveSheet.UsedRange.Columns.Count will get the number of used columns and rows in the active worksheet.

Upvotes: 0

rohitrk89
rohitrk89

Reputation: 126

i was in same situation once after some googling i was able to do, I dont have my script right now, but your code looks easy and with below changes you can achieve what you are looking for. give it a try.

I assume C is column counts for which you need to do this merging and 308 is row count you have..

Sub abc() Dim a As Integer Dim b As Integer Dim c As Integer For c = 1 To 4 b = 3 For a = b - 1 To 308 If Cells(a, c) = "" Then Range(Cells(a, c), Cells(a - 1, c)).Merge Else b = a End If Next Next End Sub

Upvotes: 2

Related Questions