Filling in some blanks to a data table

  • Thread starter Thread starter John Menken
  • Start date Start date
J

John Menken

Hello everyone. I'm back with another 'data manipulation' kind of
question. I'm using Excel 2010 and I have a weekly table similar to
the one that you see in Figure 1 below. The last field, entitled
Location has some blank entries in it and this is the way it comes out
of the system and I have no control over that aspect of it. I want my
code to simply go down the Location column and anywhere it finds a
blank space insert the words NO LOCATION. Please see Figure 2 for what
the end result should look like. Can anyone help me out with the code
that would make this happen? Thank you very much.

Fig 1.

FirstName Lastname EmpID MgrEmpID
Email Location
Alex Anderson aaaa abcxy
(e-mail address removed) Philadelphia
Brooke Brown bbbb aaaa
(e-mail address removed) Philadelphia
Carol Clark cccc aaaa
(e-mail address removed)
David Drew dddd aaaa
(e-mail address removed) Philadelphia
Erin Eisley eeee bbbb
(e-mail address removed)
Felicia Ford ffff
bbbb (e-mail address removed) Phoenix


Fig 2.

FirstName Lastname EmpID MgrEmpID
Email Location
Alex Anderson aaaa abcxy
(e-mail address removed) Philadelphia
Brooke Brown bbbb aaaa
(e-mail address removed) Philadelphia
Carol Clark cccc aaaa
(e-mail address removed) NO LOCATION
David Drew dddd aaaa
(e-mail address removed) Philadelphia
Erin Eisley eeee bbbb
(e-mail address removed) NO LOCATION
Felicia Ford ffff
bbbb (e-mail address removed) Phoenix
 
'Fill empty cells in the last column of a block of data
With ActiveSheet.Range("A1").CurrentRegion
.Columns(.Columns.Count).SpecialCells(xlCellTypeBlanks).Value =
"No Location"
End With
 
Thank you very much.
When I ran the code it kept throwing an error of "No cells found,"
then I realized that some of the cells only appear blank when they
actually contain a space. Sorry for not noticing that earlier. When re-
testing insuring that the contents of the cell were indeed empty, the
code you showed me ran perfect. Question: Is it possible to modify the
code so that it also changes cells with a space in them to the words
"No Location." Sorry for not noticing that before. Thanks again.
 
Thank you very much.
When I ran the code it kept throwing an error of "No cells found,"
then I realized that some of the cells only appear blank when they
actually contain a space. Sorry for not noticing that earlier. When re-
testing insuring that the contents of the cell were indeed empty, the
code you showed me ran perfect. Question: Is it possible to modify the
code so that it also changes cells with a space in them to the words
"No Location." Sorry for not noticing that before. Thanks again.
Click to expand...

Hi John

Modify to suit

'1200 represents how far down the spreadsheet you need to go, rather
then check 65k + cells, you can set the upper limit.

'2 represents the starting cell as it assumes the 1st row is your Header.

finalrow = Cells(1200, 1).End(xlUp).Row
For i = 2 To finalrow
If Not Cells(i, 6).Value <> "" Then
Cells(i, 6).Value = "No Location"
End If
Next i

HTH
Mick
 
Back
Top