wembleybear
New Member
- Joined
- May 1, 2013
- Messages
- 6
I some VBA which fills down the blanks from the cell above for 4 columns for reports exported into Excel from our ERP. However I have some problems with it. What I really want to do is:
1) Run the VBA only on the selection the user has made because some reports might have 4 columns this filling action is required on, but others only 1 or 2.
2) Also the VBA in it's current format looks at the whole of columns E to H. The report has header rows which should remain in place (currently I delete these rows before running but I don't want to).
3) The filling of blank cells should stop when it gets to the last column cell to the immediate right of the selection the user has made (currently it runs on for several rows after the last row even though there's nothing in the rest of those rows)
Here is the code, any help would be appreciated:
Sub FillDownAreas()
For Each area In Columns("E:H").SpecialCells(xlCellTypeBlanks)
If area.Cells.Row <= ActiveSheet.UsedRange.Rows.Count Then
area.Cells = Range(area.Address).Offset(-1, 0).Value
End If
Next area
MsgBox "Macro Finished!", vbOKOnly, ""
End Sub
1) Run the VBA only on the selection the user has made because some reports might have 4 columns this filling action is required on, but others only 1 or 2.
2) Also the VBA in it's current format looks at the whole of columns E to H. The report has header rows which should remain in place (currently I delete these rows before running but I don't want to).
3) The filling of blank cells should stop when it gets to the last column cell to the immediate right of the selection the user has made (currently it runs on for several rows after the last row even though there's nothing in the rest of those rows)
Here is the code, any help would be appreciated:
Sub FillDownAreas()
For Each area In Columns("E:H").SpecialCells(xlCellTypeBlanks)
If area.Cells.Row <= ActiveSheet.UsedRange.Rows.Count Then
area.Cells = Range(area.Address).Offset(-1, 0).Value
End If
Next area
MsgBox "Macro Finished!", vbOKOnly, ""
End Sub