I am trying to create a dynamic range that will bring back all results from a fixed set of cells, unfortunately I'm having trouble with the Range.End
method since there are blanks in between. For Example there may be no values in A8:A10
but it may start on A11
and that row will have data in it. All the tables will be different but it's fixed to the cell range below.
I also saw Range.Find
but I could not get that to work either.
The cell range is: A8:F18
lastRow = ThisWorkbook.Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
ThisWorkbook.Worksheets("Sheet2").Range("B" & lastRow).Formula = _
Source.Worksheets("SUMMARY DATA SHEET").Range("A8").Value
ThisWorkbook.Worksheets("Sheet2").Range("D" & lastRow).Formula = _
Source.Worksheets("SUMMARY DATA SHEET").Range("D8").Value
ThisWorkbook.Worksheets("Sheet2").Range("A" & lastRow).Formula = _
Source.Worksheets("SUMMARY DATA SHEET").Range("B4").Value
ThisWorkbook.Worksheets("Sheet2").Range("E" & lastRow).Formula = _
Source.Worksheets("SUMMARY DATA SHEET").Range("F8").Value
ThisWorkbook.Worksheets("Sheet2").Range("C" & lastRow).Formula = _
Source.Worksheets("SUMMARY DATA SHEET").Range("E8").Value
A B C D E F
10 vendor1 ACH vendor1 500.00 XXX 0118-CLIENT-XLIVES
11 vendor2 ACH vendor2 600.00 XX 0118-CLIENT-XLIVES
12 vendor3 ACH vendor3 400.00 XX 0118-CLIENT-XLIVES
13 vendor4 ACH vendor4 $900.00 XXX 0118-CLIENT-XLIVES
14 vendor5 ACH vendor5 $6,000.00 0118-CLIENT-XLIVES
15 vendor6 ACH vendor6 $800.00 0118-CLIENT-XLIVES
16 vendor7 ACH vendor7 $88,000.00 0118-CLIENT-XLIVES
See Question&Answers more detail:os