Variable coping

[av8tordude]

In the attach file, row 4 has a note (which in some cases is not there). In the code below, it works perfect if the "Note section" (Row 4) is not there, but if it is there, then it does not allow me to import the data in my workbook correctly. Sooo...May I receive some assistance to adjust the code, instead of starting at "A4" to copy the data, look in column A for the instance of "Alabama" and begin coping there.



With Rates_Sheet
last_Row = .Range("A" & .Rows.Count).End(xlUp).Row
Set Range2Copy = Application.Union(.Range("A4:E" & last_Row), .Range("G4:G" & last_Row))
End With

[av8tordude]

Still looking for assistance..Thanks

[Kenneth Hobs]

Tip: I first look for threads with 0 responses to see if I can help. After a day, you can reply with "bump" to bump your thread to the top for a forum that is very active.

Sub ken() Dim Rates_Sheet As Worksheet, Range2Copy As Range, first_Row As Long
Dim last_Row As Long, r As Range, f As Range
Set Rates_Sheet = Worksheets("Sheet1")
With Rates_Sheet
Set r = .Range("A" & .Rows.Count).End(xlUp)
last_Row = r.Row
Debug.Print last_Row
Set f = Range("A2:A" & last_Row).Find(What:="AL", _
After:=r, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If f Is Nothing Then Exit Sub
first_Row = f.Row
'Set Range2Copy = Application.Union(.Range("A4:E" & last_Row), .Range("G4:G" & last_Row))
Set Range2Copy = Application.Union(.Range("A" & first_Row & ":E" & last_Row), _
.Range("G" & first_Row & ":G" & last_Row))
End With
Debug.Print Range2Copy.Address(external:=True)
End Sub

[av8tordude]

Hi Ken...

Using your code, it was causing my workbook to crash. I'm not sure if its because of the limited code I provided and you adding extras to test your code, but I worked out my problem using pieces of your code. (see below). Anyways, thank you very much for your help! :-)


With Rates_Sheet
Set f = .Columns("A").Find(what:="Alabama", LookIn:=xlValues, lookat:=xlWhole)
last_Row = .Range("A" & .Rows.Count).End(xlUp).Row
Set Range2Copy = Application.Union(.Range("A" & f.Row & ":E" & last_Row), .Range("G" & f.Row & ":G" & last_Row))
End With