Hello all,
How do I select a range in column G starting from the 3rd row till the word "total" occurs in column A?
The word "total" is always the last value in column A, so perhaps starting from the bottom and offset it with 2 to select the corresponding cell in column C.
Now I need to resize the selection to the top of column C3
But I can't get it done.Sub test() Range("A1").End(xlDown).Offset(0, 2).Select End Sub
Any suggestions?
This?Range("C3", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Select
You could use this to set the range for Column G based on the word "total" being in Column A
Where foundRange is the range from G3 to Gx where x is the row number where Column A has the word "total"Sub setRNG() Dim fCell As Range, lCell As Range, foundRange As Range Dim fAdd As String With Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) Set lCell = .Cells(.Cells.Count) End With Set fCell = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find(what:="total", after:=lCell) If Not fCell Is Nothing Then fAdd = fCell.Row Set foundRange = Range("G3:G" & fAdd) foundRange.Select Else MsgBox "Cannot find the word Total" End If End Sub
Edit - I did not see that total was always the last row in Column A - you would not need something as elaborate as above you would only have to find the last row number of column A
Did not read your second postSet foundRange = Range("G3:G" & Cells(Rows.Count, "A").End(xlUp).Row)
Last edited by smuzoen; 01-24-2012 at 07:33 AM.
Hope this helps.
Anthony
Pack my box with five dozen liquor jugs
PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated”
cells(3,3).resize(cells(rows.count,3).end(xlup).row-2)
Thank you guys,
I used StephenR's code, works fine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks