copy data to next value on several columns

[jocote46]

I need your expertise on this, i have this macro but it only works on column A, what if i need the macro to work or do the same thing on multiple columns. i have a big file where i need the macro to copy the data down to the next cell value , etc. until last row.

 Sub MacroFillAreas()

    For Each area In Columns("A:A").SpecialCells(xlCellTypeBlanks)
        If area.Cells.Row <= ActiveSheet.UsedRange.Rows.Count Then
            area.Cells = Range(area.Address).Offset(-1, 0).Value
        End If
    Next area
End Sub

i know it maybe a simple edit but i tried and i'm getting errors. thanks so much in advance.

 

[Legacy 143009]

A simple example might be the one like below:

Sub MacroFillAreas()
  Dim lRow As Integer
 
  For i = 1 To 5 'This will look for until column E. You may increase this as much as you need.
    lRow = Worksheets("OldSheet").Cells(Rows.Count, i).End(xlUp).Row 'Get the last row of that column.
    For ii = 1 To lRow
      Worksheets("NewSheet").Cells(ii, i).Value = Worksheets("OldSheet").Cells(ii, i).Value 'Copy that cell from one sheet to another
    Next
  Next
End Sub

For instance you may add a condition for non-blank cells:

Sub MacroFillAreas()
  Dim lRow As Integer
 
  For i = 1 To 5 'This will look for until column E. You may increase this as much as you need.
    lRow = Worksheets("OldSheet").Cells(Rows.Count, i).End(xlUp).Row 'Get the last row of that column.
    For ii = 1 To lRow
      If Worksheets("OldSheet").Cells(ii, i).Value <> "" Then 'If old value is not empty
        Worksheets("NewSheet").Cells(ii, i).Value = Worksheets("OldSheet").Cells(ii, i).Value 'Copy that cell from one sheet to another
      End If
    Next
  Next
End Sub

 

[Peter_SSs]

That macro is a fairly inefficient way to fill blanks in a column. However, to suggest the best alternative may require a bit more detail about just what you have, where it is and exactly what you are trying to achieve.

As one example though, if you want to fill all blank cells in the worksheet's UsedRange with the value from the cell above (except row 1 of course since there is no cell above) then you could try this.
This example assumes that the worksheet does not contain formulas that need to be retained.
Test on a copy of the workbook.

Sub MacroFillAreas_v2()
  With ActiveSheet.UsedRange.Offset(1)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[jocote46]

Thank you so much! really appreciate your help. this works.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.