Macro to carry a date down a column

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
194
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

In cell A2 I have a date. I want to copy that date from A3 down to the last row that would have data in Column F
I can get it with the code below BUT the last row is always different so it would need to be changed daily.
For example today the last row was 397 but tomorrow it may be 390 or 420, etc.

Thanks in advance!!


Code:
Sub CopyDate()'
' CopyDate Macro
'


'
    Selection.Copy
    Range("A3:A397").Select
    ActiveSheet.Paste
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Re: Need help with a macro to carry a date down a column

How about
Code:
 Range("A2", Range("F" & Rows.count).End(xlUp).Offset(, -5)).Filldown
 
Upvote 0
Re: Need help with a macro to carry a date down a column

Once again FLUFF,

THANK YOU!! You are far to kind!
 
Upvote 0
Re: Need help with a macro to carry a date down a column

Glad to help & thanks for the feedback
 
Upvote 0
Re: Need help with a macro to carry a date down a column

Hi Fluff,

Not sure what happened and I didn't notice it yesterday but only tried the code once or twice.
In column B I have many rows of UPC's. For some reason when I run the code it is copying A2 & B2
all the way down instead of just A2?

Thank you!


Code:
Sub CopyDate()'
'
Range("A2", Range("G" & Rows.Count).End(xlUp).Offset(, -5)).FillDown


End Sub
 
Last edited:
Upvote 0
Re: Need help with a macro to carry a date down a column

Fluff,

My bad! I changed the column from F to G but failed to change the -5 to -6.

All good.

Thank you!!!
 
Upvote 0
Re: Need help with a macro to carry a date down a column

Hi Fluff,

Oversight on my part. I needed to insert a new column which is now column A for a couple of buttons.
Wondering if there is a way to edit the macro below to not delete anything in column A? I tried a couple of
things like starting the delete on line 5 but it keeps some junk that I don't want in B:H.
Thanks!


Code:
Sub DeleteBlankRows()   With Range("H1", Range("H" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
   Rows(Range("H" & Rows.Count).End(xlUp).Row & ":" & Rows.Count).Delete
End Sub
 
Last edited:
Upvote 0
Re: Need help with a macro to carry a date down a column

Try
Code:
Sub Buns1976()
   Dim Rng As Range
   With Range("H1", Range("H" & Rows.count).End(xlUp))
      For Each Rng In .SpecialCells(xlBlanks).Areas
         Rng.Offset(, -6).Resize(, 40).Delete xlUp
      Next Rng
   End With
   Rows(Range("H" & Rows.count).End(xlUp).Row & ":" & Rows.count).Delete
End Sub
 
Upvote 0
Re: Need help with a macro to carry a date down a column

Hi Fluff,

That appears to do the trick.

Thanks so much!!
 
Upvote 0
Re: Need help with a macro to carry a date down a column

You're welcome
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top