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

Rod_Bowyer · #1 03-25-2016, 04:29 PM

I am downloading data from the internet into an Excel spreadsheet. The data varies each time in terms of the number of rows.

I download the data into Column B (there are a number of other columns but I don't think that they matter cuz they are all have the same number of rows - which changes each time I do another download).

I download the name field into Cell A1 and then manually copy it down to each row which contains data about that customer.

When I recorded a macro to copy and paste this customer name into Column A it copies the cell (A1) and then goes to column B - end down to go to the bottom of the data then into column A and shift up arrow to take it to the entire range that I want to copy the name into and then down one. The trouble is that the Macro "anchors" itself at the bottom cell so that each time I run the macro it inserts the customer name the same number of times despite the fact that the number of rows varies each time.

Is it possible to have a macro which will copy the customer name down each time according to how many rows of data the customer has (in Column B)?

Many thanks - Rod Bowyer

macropod · #2 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 · #3 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!

Philb1 · #4 03-25-2016, 07:18 PM

Hi Rod
Do you have a sample file you can upload?

Rod_Bowyer · #5 03-25-2016, 07:54 PM

Hi Phil,

My abbreviated spreadsheet is attached. All I am trying to do is to copy the customer name down next to all rows in columns B and C with data in them. I have to do this many times and the number of rows alters each time.

The Macro which Paul gave me works wonderfully but carries on down to the end of the formatting (in the larger spreadsheet I have it set up as a table) and when the number of records changes each time the formatting stays the same for some reason or another and I cannot delete it (if any of that makes sense to you).

Many thanks - Rod

Philb1 · #6 03-25-2016, 08:07 PM

If you already have one that works that's good
I think I must have clicked the thread, read it, got sidetracked & forgot about it. When I posted for the sample there were no replies, after it posted, you were all but done

macropod · #7 03-25-2016, 08:18 PM

Quote:

Originally Posted by Rod_Bowyer

The Macro which Paul gave me works wonderfully but carries on down to the end of the formatting (in the larger spreadsheet I have it set up as a table) and when the number of records changes each time the formatting stays the same for some reason or another and I cannot delete it (if any of that makes sense to you).

Your previous post didn't include such detail, so it wasn't allowed for. To handle that, change:
.Cells.SpecialCells(xlCellTypeLastCell).Row
to:
.Range("B" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(x lUp).Row

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