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

Bill_Kro

New Member
Joined
Oct 28, 2017
Messages
14
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You could do something like
Code:
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?
 
Upvote 0
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
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)...
Code:
[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).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top