CODEWITHSUNDEEP
excelvba
BMRobin
BMRobin

Reputation: 75

VBA help in selecting final row and choosing range

The following code is doing the following:

  1. Starting from A1, it searches for the last filled cell and selects the cell right after it.
  2. After selecting the first available blank cell it selects the entire row
  3. The entire row is then colored with color index 16.

Here you go:

Sub Macro1()

Range("A1").End(xlDown).Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
    .ColorIndex = 16
    End With
End Sub

This works great but I am having difficulty selecting only columns A to H within that row. I don't want to select the entire row.

Can someone please help me? I keep getting End With errors and not sure how to fix it :S

Thank you very much for your time and consideration!

Upvotes: 0

Views: 1221

Answers (3)

asp8811
asp8811

Reputation: 803

Try this:

'Declare Variables
Dim rowNum as integer
Dim YourRange as range

'Find last row by counting cells not empty and adding 1
'Assumes that all cells from A1 down are filled. Change the +1 accordingly if not
rowNum = worksheetfunction.counta(activesheet.columns(1))+1

'Set range object to column A through H of your row number
Set YourRange = Activesheet.Range("A" & rowNum & ":H" & rowNum)

'Set interior color of the rnage object instead of using Selection
YourRange.interior.colorindex=16

Upvotes: 1

Matt Cremeens
Matt Cremeens

Reputation: 5151

That is too much selecting! How about 

Sub Macro1()
currentRow = Range("A1").end(xldown).offset(1,0).row
Range(cells(currentRow, 1), cells(currentRow,8)).Interior.ColorIndex = 16
cells(currentRow,1)= " "
End Sub

Does that work?

Upvotes: 1

Jonathan Applebaum
Jonathan Applebaum

Reputation: 5986

you can set a range for your selection, also here is another method to find last row from the end of the column (version 1) - the (Rows.Count,1) represents column A (Rows.Count,2) would represents column B and so on...

this is version one: start looking for empty row from the end of column A:

  Dim rowNum As Integer
    rowNum = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Dim rng As Range
    Set rng = Range(Cells(rowNum, 1), Cells(rowNum, 8))
    rng.Interior.ColorIndex = 16

this is version two: start looking for empty row from the start (row 1) of column A:

Dim rowNum As Integer
rowNum = Range("A1").End(xlDown).Offset(1, 0).Row
Dim rng As Range
Set rng = Range(Cells(rowNum, 1), Cells(rowNum, 8))
rng.Interior.ColorIndex = 16

Upvotes: 1

Related Questions