Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill Blanks (2003)

    I have a spreadsheet that was copied from a mainframe. I am trying to fill blanks with what is in the previous cell but the macro is not working. It is like the cells are not blank. Here is the macro:

    Sub FillBlanks2()
    Dim rng As Range
    On Error Resume Next
    Set rng = ActiveSheet.UsedRange.Columns("A:A")
    With rng
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial xlPasteValues
    End With
    Set rng = Nothing

    End Sub

    How would I adjust this to fill in the blanks if the cells look blank but they are not - or is there a way to change what seems to be blank cells to truly blank cells?

    Thanks for your help. I hope I have explained this OK.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill Blanks (2003)

    Could you attach a small sample workbook with some of the non-blank blank-looking cells? That would enable us to determine what they contain. You can remove the rest. (Please indicate which cells we should lool at)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Blanks (2003)

    This is the first column of the file but the macro does not work for that column. Thanks for your help

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill Blanks (2003)

    The "empty" cells contain spaces. You can use this macro:
    <code>
    Sub FillBlanks2()
    Dim rng As Range
    Application.ScreenUpdating = False
    For Each rng In ActiveSheet.UsedRange.Columns(1).Cells
    If Trim(rng) = "" Then
    rng = rng.Offset(-1, 0)
    End If
    Next rng
    Set rng = Nothing
    Application.ScreenUpdating = True
    End Sub</code>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill Blanks (2003)

    Linda

    You seem to have a load of char(32) characters chucked in there by the server during download


    try adding

    Columns("A:A").Select
    Selection.Replace What:=Chr(32), Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    before

    Set rng = ActiveSheet.UsedRange.Columns("A:A")
    .....

    Should work now.
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill Blanks (2003)

    Jerry, your Replace would also scrap all spaces between words in cells. In the workbook attached by Linda, that wouldn't be a problem, but if there are entries consisting of multiple words in the real workbook, it would.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill Blanks (2003)

    Good point... I was working with the book at hand.

    I suppose it would be fair to tell Linda how I found the characters ( you may have a different way) but I initially found the last used cell using:

    1) Range("A65536").End(xlup).Select

    This immediately went to a "blank cell" so

    2) I selected the cell next to it and found if it had a length using the Len function

    This resulted in a length of 10

    3) I therefore put this =Code(left(A46653, 1)) which gave 32
    Jerry

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Blanks (2003)

    Thanks so much for the macro. I have one little question. How and where would I modify it to include more than 1 column?
    For Each rng In ActiveSheet.UsedRange.Columns(1).Cells
    I know that 1 is column A but how would I fill blanks for say columns A through D?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill Blanks (2003)

    Instead of Columns(1), you can use Columns("A").

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •