Dynamic Range based on column cell content.

[Stan101]

I have a spreadsheet sent to me with various data I need to separate into two chunks of data and copy each to another worksheet. I am stuck trying to get the end row of the first range and the start row of the second range. I would prefer them as two separate subs if possible. That makes it easier for me to learn from.


For the first range I need to copy column A to column H and down to the first row that has a blank cell in column B after row A.

I have tried this code but I keep screwing it up:

Sub DynamicRange()
'Best used when column length is static

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Stud Wall Properties")
Set StartCell = Range("A1")

'Refresh UsedRange
  Worksheets("Stud Wall Properties").UsedRange


'Find Last Row
'LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'This is only finding the very last empty row. Not what I want.


'''''''''''THis is the part I keep screwing up. I have tried numerous things but I keep getting it wrong.
  
  
LastRow = Range("B2").End(xlDown).Offset(1, 0)
'Step 3: Select the next row down
 'Cells(LastRow, 1).Offset(1, 0).Row

'Select Range
  sht.Range("A1:H" & LastRow).Select

Also, instead of using the first row that has a blank cell in column B after row A as the location, as I mentioned above, alternatively I could use a known fixed cell content in Column A and make my range end in the row above the row that holds this fixed cell content. Let's call this fixed cell content "cookies".



The second range again needs columns A to H. The range needs to start at the row where the known fixed cell content (cookies) is located in Column A. I need to finish the range at the last used row.

I think I can handle selection the end row because I have managed that with the code above, but I have no idea how to start the dynamic range based on finding "cookies".

 

[Oscar Cronquist]

Lastrow = sht.Range("A" & Rows.Count).End(xlUp).Row

 

[Fluff]

How about

Sub DynamicRange()
'Best used when column length is static

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Stud Wall Properties")
Set StartCell = sht.Range("A1")

'Refresh UsedRange
sht.UsedRange


LastRow = StartCell.Offset(, 1).End(xlDown).Offset(1, 0).Row
sht.Range("A1:H" & LastRow).Select

for part 1

 

[Stan101]

Thanks for your assistance, guys. But I could not get the result I need yet. I will keep trying.