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:
[vba]
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
[/vba]
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
Hi Ross and everybody
[VBA]
Sub fill_inBIS()
Dim cell As Object
Set vide = ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
For Each cell In vide
cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub
[/VBA]
Is this way is similar with your’s?
Is this syntax is good?
Or is it a bad path to take?
Sorry for my bad english
(I’ve used Google translation)
Hi,
That looks fine, your code uses “used range” with mine it’s what the user actually slectes.
Thanks
Ross