Populate cell with value where empty

dpmaki

Board Regular
Joined
Sep 12, 2011
Messages
165
I need to come up with some code that will allow me to put in a certain value "555555" where any values in column N are empty. I need to use M as the driver here meaning that I need to xlup on column M and offset by 1 so that I can apply the 555555 value to all the empty cells in N down to the last populated cell in column M. Anyone have any idea on how to do this?

excel-2.jpg
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
dpmaki,

Give this a try:
Code:
Sub tgr()
    
    On Error Resume Next
    Intersect(ActiveSheet.UsedRange, Columns("N")).SpecialCells(xlCellTypeBlanks).Value = 55555
    
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
This should do what you asked for...

Code:
Range("N1:N" & Cells(Rows.Count, "M").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = 55555
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
dpmaki,

Give this a try:
Code:
Sub tgr()
 
    On Error Resume Next
    Intersect(ActiveSheet.[COLOR=red]UsedRange[/COLOR], Columns("N")).SpecialCells(xlCellTypeBlanks).Value = 55555
 
End Sub
The use of UsedRange could possibly overfill Column N (one, if its last row is not set correctly because of cell editing or, two, if other columns have more data than Column M, say, because of a Total cell at the bottom of some column).
 

dpmaki

Board Regular
Joined
Sep 12, 2011
Messages
165

ADVERTISEMENT

The use of UsedRange could possibly overfill Column N (one, if its last row is not set correctly because of cell editing or, two, if other columns have more data than Column M, say, because of a Total cell at the bottom of some column).


It's trying to work, but the contents of column N where they appear NULL are actually formulas.

Example - this is what resides in N4: =IF(K4>0,Rebates!I4,"")
Would there be a better way to right the if statement so it's truly putting a NULL in there?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Perhaps change that formula to

=IF(K4>0,Rebates!I4,555555)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It's trying to work, but the contents of column N where they appear NULL are actually formulas.

Example - this is what resides in N4: =IF(K4>0,Rebates!I4,"")
Would there be a better way to right the if statement so it's truly putting a NULL in there?
Let me get this straight... you have formula in your cells show the empty string ("") and you want to overwrite the formula with a numerical constant (thus removing the formula from the cell), is that correct? If so, what is in the cells in Column N that are not blank... formulas or constants (that is, if the cell is not blank, is that because you overwrote the formula that was there originally with a numerical constants)?
 

dpmaki

Board Regular
Joined
Sep 12, 2011
Messages
165
Let me get this straight... you have formula in your cells show the empty string ("") and you want to overwrite the formula with a numerical constant (thus removing the formula from the cell), is that correct? If so, what is in the cells in Column N that are not blank... formulas or constants (that is, if the cell is not blank, is that because you overwrote the formula that was there originally with a numerical constants)?

Correct - I have formulas in column N, but not all of column N - the formula has filled those cells already that need to be filled. The thought of the if,than crossed my mind, but not all cells in the column have formulas in them. So I suppose I could use a combo of the if/and plus the code you already gave me.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
Correct - I have formulas in column N, but not all of column N - the formula has filled those cells already that need to be filled. The thought of the if,than crossed my mind, but not all cells in the column have formulas in them. So I suppose I could use a combo of the if/and plus the code you already gave me.
The cells that the formulas already filled in... do they have to stay as formulas or would it be alright to convert them to constants? If it would be alright to convert them to constants, then you could use this code...

Code:
  With Range("N1:N" & Cells(Rows.Count, "M").End(xlUp).Row)
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).Value = 55555
  End With
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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