Select a Generic Range VBA Excel 2007 1

[JustATheory]

I have this piece of code that fills in blanks within a range and I'd like it to be generic so that I can add it to a button. I'd like it to run for the current worksheet and the current range, I'm stuck on that part.

Thanks,
Andy

For Each c In Worksheets("Worksheet").Range("FillBlankTop", "FillBlankBottom")
If (c.Value) = "" Then
c.Offset(0, 0).Value = c.Offset(-1, 0).Value
End If
Next

 

[SkipVought]

Hi,

Logically what defines the top and bottom of your range?

Typically, there is a table. I recommend that your table be completely isolated from any other data on the sheet, so that referencing a cell in the heading (ONE ROW), and then the current region, references the entire table and nothing else...

With [i]a_cell_reference_in_your_table_headings[/i].CurrentRegion
  lFirstRowOfData = .Row + 1
  lLastRowOfData = .Row + .Rows.Count - 1
End With

With Worksheets("Worksheet")
  For Each c In .Range(.Cells(lFirstRowOfData, [i]YourColumn[/i]), .Cells(lLastRowOfData, [i]YourColumn[/i]))
    If (c.Value) = "" Then
        c.Value = c.Offset(-1, 0).Value
    End If
  Next
End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If you have selected a range then:

For Each c In selection
If (c.Value) = "" Then
c.Offset(0, 0).Value = c.Offset(-1, 0).Value
End If
Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JustATheory]

Skip,

Many thanks works like a charm

Andy