VBA - find empty cells, set equal to "EMPTY"

Question

Code finds values from sheets and copies them over to one sheet. If a column is completely empty, it prints "NO ITEMS".

I need to make it so, once it is done copying the items over, it finds any blank cells in column "B" (StartSht, "B") and from the range of the last occupied cell of "C" up, fills it with the string "EMPTY"

Any ideas how I would go about doing that?

It does (1) and I need it to do (2)

(1)

enter image description here

(2)

enter image description here

Set dict = GetValues(hc3.Offset(1, 0))
If dict.count > 0 Then                  
    'add the values to the master list, column 2
    Set d = StartSht.Cells(Rows.count, hc1.Column).End(xlUp).Offset(1, 0)
    d.Resize(dict.count, 1).Value = Application.Transpose(dict.items)
Else
    'if no items are under the HOLDER header
    StartSht.Range(StartSht.Cells(i, 2), StartSht.Cells(GetLastRowInColumn(StartSht, "C"), 1)) = " NO ITEMS "
End If

What specifically have you tried to find blank cells? Is there any reason all of the normal ways of finding blank cells are inappropriate, or are you just not aware of those ways? This would include the common: Range.SpecialCells(xlCellTypeBlanks) and looping through and checking Range.Value <> "". I assume you know how to set the value once you find the blanks? — Byron Wall
– Byron Wall, Commented Jun 18, 2015 at 20:07
I am very unfamiliar with it (learned that Range.SpecialCells(xlCellTypeBlanks) existed about 20 minutes ago) and I do not know how to set a range that I mentioned in the question. Every time I try, I get an error that I've set a range that isn't possible @Byron — Taylor
– Taylor, Commented Jun 18, 2015 at 20:10

Byron Wall · Accepted Answer · 2015-06-18 20:09:48Z

1

Blank cells are easy to find with the SpecialCells function. It is the same as using GoTo (or hitting F5) and choosing Blanks.

StartSheet.Range("B:B").SpecialCells(xlCellTypeBlanks).Value = "EMPTY"

You can do the same for column C after building the appropriate range.

answered Jun 18, 2015 at 20:09

Byron Wall

4,0202 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Taylor Over a year ago

Thank you for explaining! Yes that is great except for one thing. I am looping through a file so I need it to just fill the empty cells from the last filled row in C up to the top of B. Is there any way to do that? @Byron

Byron Wall Over a year ago

I guess your question says find any blank cells in column B so I keyed in on that. What does to the top of B mean? What cell address are you trying to get to? B1? Regardless, there should be plenty of other references for how to build the ranges.

Taylor Over a year ago

Sorry yes I see how I worded that poorly. I added pictures of what it does vs what I want it to do. It opens multiple files so I need it to only find the empty cells in that range. There are other references but none I have found worked for this, at least I am not implementing them correctly. I have tried dozens of different methods and nothing has given me a succesful result @Byron . Each file goes through a loop so I simply want it to fill the empty cells with EMPTY after each looop so that the cells are occupied and will not be printed over by the next loop

Martijn Pieters · Accepted Answer · 2015-06-19 20:54:55Z

1

StartSht.Range(StartSht.Cells(GetLastRowInColumn(StartSht, "B"), 2), StartSht.Cells(GetLastRowInColumn(StartSht, "C"), 1)).SpecialCells(xlCellTypeBlanks).Value = "EMPTY"

edited Jun 19, 2015 at 20:54

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Jun 19, 2015 at 13:34

Taylor

1811 gold badge4 silver badges25 bronze badges

Collectives™ on Stack Overflow

VBA - find empty cells, set equal to "EMPTY"

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related