Want to fill down using exisiting data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of regional office employees, but the regional office column has the name of the office only next to the first person in each office. This leaves several blank cells, then the next regional office in one cell, several blank cells, etc.

Can Excel intelligently fill down, using the existing regional office names to fill in the blank cells until it hits a cell with a new office name, then switching to that new name for more fills, etc.? Or do I have to auto fill for each regional office?
 
Hi

Select the column / range. Then Edit / Go to / Special / Blanks. This will
select all of the blank cells in the range. Type = and hit the up arrow and
then type Ctrl Enter. To fix these values use Copy then Paste Special /
Values.

Hope this helps

--
Andy.


Cking said:
I have a list of regional office employees, but the regional office column
Click to expand...
has the name of the office only next to the first person in each office.
This leaves several blank cells, then the next regional office in one cell,
several blank cells, etc.
Can Excel intelligently fill down, using the existing regional office
Click to expand...
names to fill in the blank cells until it hits a cell with a new office
name, then switching to that new name for more fills, etc.? Or do I have to
auto fill for each regional office?
 
Hello,

I'm trying to solve the same problem as Cking, but when I follow And
B's instructions i get a dialog "No blanks found". I've also tried th
code below (found here) with the same results "No blanks found". An
help would be appreciated.
Thanks

Using Office Excel 2003
I'm trying to make col A look like col C
as in the attached sample


Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
'col = ActiveCell.Column
'or
col = .Range("b1").Column

Set rng = .UsedRange 'try to reset the lastcell
LastRow
.Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow
col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End Wit

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56785
 
This stuff works if the cells are really blank. If they have formulas that
evaluate to "", then the cells aren't blank.

If the cells had formulas that evaluated to "" and you did an Edit|copy followed
by an edit|paste special|values, then these cells aren't really blank either.

If you have nothing but values in column A (no formulas), you can empty these
blank looking cells with another step:

with .Range(.Cells(2, col), .Cells(LastRow, col))
.value = .value
end with
set rng = nothing
'code continues.



tweakaholic < said:
Hello,

I'm trying to solve the same problem as Cking, but when I follow Andy
B's instructions i get a dialog "No blanks found". I've also tried the
code below (found here) with the same results "No blanks found". Any
help would be appreciated.
Thanks

Using Office Excel 2003
I'm trying to make col A look like col C
as in the attached sample

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
'col = ActiveCell.Column
'or
col = .Range("b1").Column

Set rng = .UsedRange 'try to reset the lastcell
LastRow =
Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow,
col)) _
Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
Value = .Value
End With

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=567854
Click to expand...
 
Dave,

Thanks for the response, apparently what I thought were "blank" cell
are not blank at all. I discovered that they each contain two spaces.

I've found several manual ways to empty the contents of the cell
containing the spaces but would prefer to add code to your origina
macro to further automate the procedure.

I don't know how to incorporate that snippet into your original macr
to test it but wouldn't it will also clear the date since it's a valu
not a formula?

Thanks,
tweakaholi
 
If the cells contain spaces (" " w/o the quotes), then the suggestion I made
won't help.

I think I'd yell at the users to stop doing this, but add something like:

Dim sCtr As Long

For sCtr = 1 To 10 'as big as you'll ever need
With ActiveSheet
.Cells.Replace What:=Space(sCtr), _
Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
Next iCtr


before the existing code got to do anything important.
 
Back
Top