put data in empty cells

Bookmaster

Board Regular
Joined
Mar 22, 2014
Messages
73
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I have workbook with one sheet full of data.
Can somebody help me with macro which will find empty cells in a row and put same date in those cells, please? I don't need to know where are those cells I just need macro to find those cells in a row and fill them with same date. Date can be e.g. 01/01/2009

Thanks

BM
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
338
Do you really need a macro for this? I mean, if it's a one-time operation, then you can use Search-and-Replace and save yourself a lot of trouble that way. Just select the range of cells that you want to operate on, and do a replace of <nothing> with a date value. Cells containing data will not be affected, and truly empty cells will contain whatever date you decide to use.

Please note that you will need to have "some value" at the bottom rightmost corner of your search-and-replace range for this to work. Excel won't perform the operation on a totally empty range.
 

Bookmaster

Board Regular
Joined
Mar 22, 2014
Messages
73
Office Version
  1. 2013
Platform
  1. Windows
Thank you for reply. So far I manage to make this code:

Cells(Rows.Count, "H").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "1/1/2009"
Cells(Rows.Count, "H").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "1/1/2009"
Cells(Rows.Count, "H").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "1/1/2009"
Range(ActiveCell, ActiveCell.End(xlDown)).Select

Now I am facing different problem. In column "G" I have data in last (bottom cell) e.g. "G7652" (but that is not always the same last cell). I would like to fill date in column "H" up to same last cell in this case "H7652". Date in column "H" will be the same.

BM
 

Watch MrExcel Video

Forum statistics

Threads
1,122,206
Messages
5,594,832
Members
413,943
Latest member
Dhornsby21

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
Top