Would like a macro to copy a cell down a variable number of rows

macropod · #1 03-25-2016, 07:10 PM

Sure, it's possible, but an ordinary macro accessed via Alt-F8 won't save much time compared to your manual process. All that would be saved is a couple of keystrokes - for which you'll need a different set of keystrokes to run the macro. For the most part, all you'd be doing is trading one set of keystrokes for another. If anything, you'd probably want a Worksheet_SelectionChange macro, perhaps coded along the lines of:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
With ActiveSheet
  .UsedRange
  Application.EnableEvents = False
  If .UsedRange.Rows.Count > 1 Then .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row).FillDown
  Application.EnableEvents = True
End With
End Sub

The alternative would be an ordinary macro assigned to a keyboard shortcut (thus saving a few keystrokes), coded along the lines of:

Code:

Sub DataFillDown()
With ActiveSheet
  .UsedRange
  If .UsedRange.Rows.Count > 1 Then .Range("A1:A" & .Cells.SpecialCells(xlCellTypeLastCell).Row).FillDown
End With
End Sub

Rod_Bowyer · #2 03-25-2016, 07:18 PM

Hi Paul, this is wonderful. It does exactly what I was looking for - thank you.

I have to import hundreds of records - all with different number of rows so this saves me quite a bit of work! Great!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail merge at large table with variable number of rows	blar	Mail Merge	1	10-19-2015 03:04 PM
How to find number of coma and then add that number of rows in word using macro?	PRA007	Word VBA	7	05-27-2015 10:45 PM
Copy Rows down with value from another Cell	burlinghamla	Excel	1	01-14-2015 06:26 AM
Creating a table for a variable number of rows	OllieOnline	Mail Merge	1	03-27-2013 02:48 PM
Using macro to add variable number of rows to a protected word table	Julia	Word Tables	1	01-09-2013 06:04 AM