Solved: Select Range of all rows

[ssinghal]

In the following code, I want to select a range from H2 to the last row of my sheet. The 62 is not constant. I need the last row to be determined by data in column b as h has no data.



Range("H2:H62").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="0.75", Formula2:="0.97"
Selection.FormatConditions(1).Interior.ColorIndex = 44
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="0.9621"
Selection.FormatConditions(2).Interior.ColorIndex = 3

[austenr]

Check out this KB entry:

http://vbaexpress.com/kb/getarticle.php?kb_id=417

[ssinghal]

This doesn't really help. I need more than just finding the last row. I need to use the last row number to select my range.

[austenr]

How about this:

Selects a Column or Row of Cells in a Used Range


Sub SelectColumn()
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell
Else
Set UpBound = ActiveCell.End(xlUp)
End If
Else
Set UpBound = ActiveCellEnd If
If ActiveCell.Row < Rows.Count Then
If IsEmpty(ActiveCell.Offset(1, 0)) Then
Set LowBound = ActiveCell Else
Set LowBound = ActiveCell.End(xlDown)
End If
Else
Set LowBound = ActiveCell
End If
Range(UpBound, LowBound).Select
Set UpBound = Nothing
Set LowBound = Nothing
End Sub

[ssinghal]

This doesnt work. Pulls up debugger.

[lucas]

what does debugger say?

[austenr]

I see it. Typing too fast. Try this:

Chage this:

Set UpBound = ActiveCellEnd If

to this:

Set UpBound = ActiveCellEnd

Unmatched If :banghead: