|There were 2 things that you didn't change which were causing the code to act badly:|
Original line that cleared the data from A2 and down:
'Clear old data from cells below Date
Range("$A$2:$B" & Rows.Count).ClearContents
Required change to clear data from B10 and down:
'Clear old data from cells in Table
Range("$B$10:$C" & Rows.Count).ClearContents
Lines you changed to copy data into Columns B & C, but starting in Row 2:
Range("$B$1").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
Range("$B$1").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
Required lines to copy the data into Columns B & C but starting in Row 10:
Range("$B$9").Offset(nxtrw, 0) = Sheets(1).Cells(cell.Row, 1)
Range("$B$9").Offset(nxtrw, 1) = Sheets(1).Cells(cell.Row, d.Column)
However, there is another issue that we need to deal with. In your example data, you had a list of names in Column A. The values you had in Columns B:D went no further down than the row containing the last name (i.e. A7 = Elgon).
In the spreadsheet, you have values in Rows that are below the last entry in Column A. For example, you have a value in G89 but no entry in A89. Based on how the code is written, it will not pick up the value in G89 because the code thinks the data ends at Row 88 (i.e A88 = Ihub job posting, A89 ="")
If Column A can be blank even though there are values further down in the table, the code needs another way to determine where to stop looking for values. I had assumed that there would always be a "name" in Column A, but I guess that's not the case.
Will the table always end at Row 96 as shown on the spreadsheet?
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03