I need assistance on how to apply a suitable Excel VBA do-loop or otherwise to copy cell content and paste the next set of empty cell(s) in each row. As it loops through each row, it copies and paste only the next empty cells and again it copies the content of the next filled cell and paste on the next empty cells until each row is filled. I need to apply this to a table that contains about 5,000 rows. Any assistance will be highly appreciated. The original Table and expected Table result should look as follows: The sample Tables
1 Answer
The below should do the trick. Please note that the below will overwrite the table with values only, so if your table has formulas, the formulas will be lost as they are overwritten with values.
Sub SuperUserHelp()
Dim TempArray As Variant, x As Long, y As Long
Dim ValueNow As Variant, SheetName As String, TableAddress As String
SheetName = "Sheet1" 'Set your sheet name here
TableAddress = "A1:E5" 'Set your table range here
TempArray = Sheets(SheetName).Range(TableAddress).Value
For x = 1 To UBound(TempArray, 1)
ValueNow = Empty 'new row defaults to
For y = 1 To UBound(TempArray, 2)
If TempArray(x, y) = Empty Then
TempArray(x, y) = ValueNow
Else
ValueNow = TempArray(x, y)
End If
Next y
Next x
Sheets(SheetName).Range(TableAddress).Value = TempArray
End Sub