Selecting rows of a range (similar to typing Shift+Spacebar)

[Bill_Kro]

I can't figure out how to select the rows of a range. I want to do something equivalent to hitting Shift+Spacebar

I have used the following code to select a range (equivalent to using shift-Ctrl-down)

Range(Selection, Selection.End(xlDown)).Select

I now want to select the entire rows but keep the active cell in the same column (in this instance it turns out to be column F)

I tried to use the following

Range(Selection, Selection.End(xlDown)).EntireRow.Select

but the ?active? cell moves to column A. I want it to stay in the same column because in the next step, I'm going to use

ActiveCell.End(xlDown).Select

and I don't want to move to column A

I also tried

Range(Selection, Selection.End(xlDown)).Rows.Select

But this doesn't seem to select rows.

 

[Fluff]

You could do something like

Dim rng As Range
Set rng = ActiveCell
Range(rng, rng.End(xlDown)).EntireRow.Select
rng.Select

But there is very rarely any need to select anything.
What is it that you are trying to do?

 

[Bill_Kro]

Fluff,

I don't think your code solves my problem.

Below is the macro that I wrote.
I works for highlighting cells, but now I want to modify it to highlight rows.
The code that I'm looking to change is red.

Sub Loop_Ctrl_Shift_Down_and_highlight_v3()

' *** Must start in a blank cell (not the first cell that you want to process

' Do the equivalent of Ctrl-Down_Arrow
ActiveCell.End(xlDown).Select

' Check not on last row of sheet
Do While ActiveCell.Row < 1048576

' Check if a single row of data (does the next cell down have data or is blank)
' If it is a single row, then highlight yellow

' This checks if the cell below is empty
If IsEmpty(ActiveCell.Offset(1, 0).Value) Then

' highlight the selected cell Yellow
With Selection.Interior
.Color = 65535 ' highlight cell yellow
End With

' If it is multiple rows, then select the multiple cells/rows
Else

' Ctrl-Shift-Down to select multiple cells
Range(Selection, Selection.End(xlDown)).Select
' Fluff code
' Dim rng As Range
' Set rng = ActiveCell
' Range(rng, rng.End(xlDown)).EntireRow.Select
' rng.Select

' highlight the selected cell Yellow
With Selection.Interior
.Color = 65535 ' highlight cell yellow
End With

' Go to bottom of the selected cells
ActiveCell.End(xlDown).Select

End If

' Go to next cell down
ActiveCell.End(xlDown).Select

' Repeat the process
Loop

' Final step is to move the cursor to cell B3 (someplace near the top)
Range("B3").Select

End Sub

 

[Fluff]

How about

Sub Bill_Kro()
   With Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
      .SpecialCells(xlConstants).EntireRow.Interior.Color = 65535
   End With
   Range("B3").Activate
End Sub

 

[Rick Rothstein]

Assuming the values in your cells are constants (that is, not formulas), then I am pretty sure this single line of code will do what you want (select any cell in the column whether blank or not)...

[table="width: 500"]
[tr]
	[td]Sub FillNonBlankCellsInColumnWithYellow()
  ActiveCell.EntireColumn.SpecialCells(xlConstants).Interior.Color = vbYellow
End Sub[/td]
[/tr]
[/table]

Note: The active cell will remain active after this code has run so if you select a cell near the top of the worksheet, you should not have to deliberately activate another cell (such as B3 in your code).