select range to the right and down

[dshafique]

Good afternoon everyone. I have a macros which goes to a certain cell, and selects all the active cells to the right and below in the certain table.

Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select

the goal is that it selects everything in that range and then colors the cells based on criteria.

For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))       
         If cl >= Cells(cl.Row, "E") Then
           cl.Interior.Color = 5287936
        End If
        Next cl

its been working fine for the most part with my reports, however, in one of my reports, I noticed when the table has only one row of data, this function selects all the rows to the right in the table, but it selects all the rows to infinity below instead of just in the table

is there a way to fix it so that it selects only the cells in the table,

or is there a way for me to put an if else loop, where "if the table has one row of data, then only select to the right, else if greater than 1, select to the right and down"

thanks

 

[Scott T]

You could look from the bottom up so that it selects only one row when there is only one row, assuming there are no blanks that may though last row off.

Dim lr As Long
lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Range(ActiveCell, Cells(lr, ActiveCell.End(xlToRight).Column)).Select

or


You could test if the cell under the active cell is blank

If ActiveCell.Offset(1, 0) <> "" Then
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
Else
    Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.End(xlToRight).Column)).Select
End If

 

[mumps]

Try:

Sub test()
    If ActiveCell.CurrentRegion.Rows.Count > 1 Then
        Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    Else
         Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.End(xlToRight).Column)).Select
    End If
End Sub

 

[Rick Rothstein]

Assuming your UsedRange starts at cell A1, here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub SelectDownAndToRight()
  On Error GoTo NotInUsedRange
  Intersect(ActiveSheet.UsedRange, Range(ActiveCell, Cells(Rows.Count, Columns.Count))).Select
NotInUsedRange:
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Try:

Sub test()
    If ActiveCell.[B][COLOR="#FF0000"]CurrentRegion[/COLOR][/B].Rows.Count > 1 Then
        Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    Else
         Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.End(xlToRight).Column)).Select
    End If
End Sub

What about if there are blank rows (or columns) and the selected cell is above (or to the left of) of that blank row (or column)?

 

[mumps]

@Rick Rothstein: Good point, Rick. Thanks.