You know the score, you've done a pivot table and there are a load of empty cells. You want to fill them up:
[NB: you have to copy the PT as values or the code will fail!]
I use this almost every day:
Sub fill_in()
Dim cell As Object
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub
Dim cell As Object
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub
I think this may be a repost - I did look back but i could not find anything that i had done before, but i remember that Zack Barresse suggesting a none vba way that involvde a special paste of some sort.
Well there you are...
Ross
I tend to use the paste method.
put an example formula in a cell, copy it
select the whole range
select special blanks, paste or paste formulas.
then paste special values if you like.
It would be easier to add your code to a code library for sure. maybe one day!
cheers
Simon
Yeah I'm proper lazy, I coded a short cut for paste values, how lazy is that - I can't spend all day clicking right click menus you know I've got ones of pound decisions to make... Ham today I think!
Hi Ross,
I figured you'd use something like this:
Sub fill_in()
Worksheets(1).UsedRange.SpecialCells(xlCellTypeBlanks).Value = "your value here"
End Sub
or replace Worksheets(1).UsedRange with Selection
Cheers,
Remco