Adding data below last used row and merging the cells

Question

I am trying to make a compare file script that'll check for a certain ticketID on file 1 that isn't on file 2, and vice versa. That compare will add the whole row to a sheet if it wasn't found in a file. I have the data separated and have text that is a description for the first block of data (the IDs in file1 not in file2).

I have attached an image of what the current output looks like (I've erased the pertinent data but the data goes in the light grey lines). I have my code ordered to where I want to insert the description text for the second compare. So I check for last row after the first compare loop.

Sheets("Sheet3").Select
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveSheet.Cells(LastRow, "A").Value = "here"
ActiveSheet.Cells(LastRow, "A").Select

My issue is that I don't know how to grab that range of cells. In my current output, I want to grab from (A15:P16), so two rows all the way to column P. I always know it'll be Row A and Column P, but I never know what row number. This is because when I compare files I won't know how many differences I will have and therefore how many rows are used in sheet3. Once I have this range selected or available, I'd like to put in my description text for the second block of data and be able to merge and indent and customize it so I can have it where I want. Then I will do the second compare and insert the data from file 2 not found in file1. So the main issue is I don't know how to grab that range of cells I want because I won't know the exact rows it'll be on.

Not sure why it didn't add the picture, but here it is. I don't have enough reputation yet to post pictures. http://i.stack.imgur.com/zzXK1.png — rdan1, Jun 22 '16 at 17:04
Will it always start in Row 4? Or always start in a specific row? — Kyle, Jun 22 '16 at 17:07
Selecting a range of cells can be done by: ActiveSheet.Range(Cells(15,1),Cells(16,16)). The cells() function takes two parameters, a Row Index and a Column Index, both are numbers. — Ryan Wildry, Jun 22 '16 at 17:10
oh ok nice, so I can add in ActiveSheet.Range(Cells(LastRow,1),Cells(LastRow + 1,16)) ? — rdan1, Jun 22 '16 at 17:12
Also, *avoid using [`.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)* — BruceWayne, Jun 22 '16 at 17:15
@rdan1 your statement is correct. Assuming your LastRow is holding the correct value. — Ryan Wildry, Jun 22 '16 at 17:20

score 0 · Answer 1 · answered Jun 22 '16 at 17:15

To select the range requested, I would use:

With ActiveSheet
    .Range("A4:P" & .cells(rows.count,1).end(xlup).row).Select
End with

I must recommend not selecting this range though. I recommend performing whatever action is necessary directly on the range.

With ActiveSheet
    .Range("A4:P" & .cells(rows.count,1).end(xlup).row).Merge
End with

Adding data below last used row and merging the cells

1 Answers1