Macro to carry a date down a column

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,311
Office Version
  1. 365
Platform
  1. Windows
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
 

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
Re: Need help with a macro to carry a date down a column

Once again FLUFF,

THANK YOU!! You are far to kind!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,311
Office Version
  1. 365
Platform
  1. Windows
Re: Need help with a macro to carry a date down a column

Glad to help & thanks for the feedback
 

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138

ADVERTISEMENT

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:

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
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!!!
 

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138

ADVERTISEMENT

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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,311
Office Version
  1. 365
Platform
  1. Windows
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
 

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
Re: Need help with a macro to carry a date down a column

Hi Fluff,

That appears to do the trick.

Thanks so much!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,311
Office Version
  1. 365
Platform
  1. Windows
Re: Need help with a macro to carry a date down a column

You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top