CODEWITHSUNDEEP
vbaexcelrange
Thomas
Thomas

Reputation: 3

Setting top row of range in vba to be based on a cell value

I'm new to vba coding and am trying to write code at my current job that will combine multiple excel spreadsheets into one (by appending them). The main problem is that the # of rows as well as the starting row for the ranges that I need to copy from each spreadsheet differ across files.

Specifics: My datasets each have a header, "Opportunity Name," that is in column A and that can typically be found in row 17 but it can vary. I want the top row of my range to start in the row immediately below Opportunity Name so my best guess is to try and get the code to search for that value, find the row number + 1, and perhaps store that as an object (FirstRow in the below example?) to be used in the range function. But as you can see in the code below, the only range syntax I've been able to run starts in A17 each time. Please help point me in the right direction to change that A17 to be variable or if there's a better way to go about this.

For nfile = LBound(SelectedFiles) To UBound(SelectedFiles)
    FileName = SelectedFiles(nfile)
    Set wb = Workbooks.Open(FileName)
    LastRow = wb.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    FirstRow = wb.Worksheets(1).Cells.Find("Opportunity Name").End(xlUp).Row
    Set SourceRange = wb.Worksheets(1).Range("**A17**:AB" & LastRow)
    Set DestRange = Sheet1.Range("A" & nrow)
    Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)

    DestRange.Value = SourceRange.Value

    nrow = DestRange.Rows.Count + nrow

    wb.Close savechanges = False

Next nfile

Thanks,

Thomas

Upvotes: 0

Views: 1866

Answers (1)

BigBen
BigBen

Reputation: 50007

You don't need .End(xlUp).Row when you're finding FirstRow.

...
FirstRow = wb.Worksheets(1).Cells.Find("Opportunity Name").Row + 1
Set SourceRange = wb.Worksheets(1).Range("A" & FirstRow & ":AB" & LastRow)
...

N.B. You could also add error checking to make sure that "Opportunity Name" is found.

Upvotes: 1

Related Questions