Variable Range

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

Hello. I am using the code below to look down Column G
and locate the last entry for "1". I then need to move
down 1 row from there and CUT all remaining data (Columns
A through G) and paste onto Sheet2. The last entry
for "1" varies from day-to-day.

For some reason, the code picks-up the Range ("G12816")
as the starting point for the CUT opperation (See Line #
13).

Is there a way to begin the Cut Operation 1 row below
where the last entry for "1" is found in Column G ?

Thanks for your Guidance.


1.Sheets("1").Select
2. Dim rng As Range
3. Set rng = _
4. Columns(7).Find(What:="1", After:=Cells
(Rows.Count, "g"), _
5. LookIn:=xlFormulas, LookAt:=xlWhole, _
6. SearchOrder:=xlByRows, SearchDirection:=xlPrevious,
_
7. MatchCase:=False)


8. If Not rng Is Nothing Then
9. rng.Select
10. Else
11. MsgBox "1 not found"
12. End If

'cut engine 2
13. Range("G12816").Select
14. Range(Selection, Selection.End(xlToLeft)).Select
15. Range(Selection, Selection.End(xlDown)).Select
16. Selection.Cut
17. Sheets("2").Select
18. Range("A1").Select
19. ActiveSheet.Paste
 
Change line 13 to rng.Offset(1,0).Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top