Fill Down Macro Not Working

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
I have a fill down to copy data to the last row of data. Currently, my spreadsheet has 437 rows of data. Data exists in A, B, C, D, E, F. For some reason, the macro is stopping at row 393. I checked rows 394 and beyond in column G and all columns are blank. Any idea as to why this is happening?

The DIM USDRWS....etc is in an earlier part of the macro so I don't have it listed above the portion of the macro I have included.
Dim UsdRws As Long
UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row



Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&TEXT(RC[-1],"" MM/DD/YY"")"
Range("g2:g" & UsdRws).FillDown
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
What is the value of UsdRws when you get to the filldown section of code?
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
is this what your looking for?

Dim UsdRws As Long
UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
When the code gets to this line
VBA Code:
 Range("g2:g" & UsdRws).FillDown
what is the value stored in the UsdRws variable?
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365

ADVERTISEMENT

I am sorry, I am not 100% sure what you are asking me?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Add this msgbox as shown
VBA Code:
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&TEXT(RC[-1],"" MM/DD/YY"")"
MsgBox UsdRws
Range("g2:g" & UsdRws).FillDown
then run the code, what does the message box say?
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365

ADVERTISEMENT

So now I am running the macro and it's bringing the data down to row 785? Again, my last row of data is 437....here are the messages I got when I ran you VBA.


1601577281961.png


1601577312202.png


1601577346776.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
How about something like this instead?
VBA Code:
    Dim lr As Long
'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row
'   Populate all cells of columnG with formula
    Range("G2:G" & lr).FormulaR1C1 = "=RC[-2]&TEXT(RC[-1],"" MM/DD/YY"")"
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
It looks good!!! Thank you for your help AND your quick responses!!! :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help.

One thing to note there. If you want to apply the same formula to all the cells, it isn't necessary to use Fill Down. You can apply the formula to the whole range at once, like I did in the last line of the code I posted. It also helps make the code a little shorter.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,929
Messages
5,545,082
Members
410,652
Latest member
Zot
Top