Creating a NUL Cell

  • Thread starter Thread starter John Baker
  • Start date Start date
J

John Baker

Hi:
Excell 2000!

I need to do something simple, so that i can find the bottom of a range.

I have a range from a1 to c1000, all of which have formlae in them. I would like to be
able to set them to null (NOT SPACE OR ""), when a specific conditions exists.

I want to be able to do a

Range("A1").Select
Selection.End(xlDown).Select
iRow = ActiveCell.Row
iColumn = ActiveCell.Column
Cells(iRow, iColumn+3).Select

to select all the cells that have an entry, since cell entry will go from top to bottom.
This code unfortunately selects ALL the cells in the a1:c1000 range (since they all have
the formula in them). I tried extracting cells that had non space entries and that didn't
work either.

Help!!!

John Baker
 
John

I think you'll have to loop through column A until you get to a "blank"
cell. Try this

Sub GetRange()

Dim Rng As Range
Dim cell As Range
Dim lRow As Long

For Each cell In Range("a1", Range("a1").End(xlDown)).Cells
If cell.Value <> "" Then
lRow = cell.Row
Else
Exit For
End If
Next cell

Set Rng = Range("a1", "c" & lRow)

MsgBox Rng.Address

End Sub
 
Back
Top