Formatting with VBA

[jose001]

Hey everyone,

Not sure if this is a complicated problem or not but it is well out of my reach. I have a spreadsheet with data in the first two rows A-H. There may also be more data though in columns D&E. I want to put some code into my VBA form which will check what is the last row with data in it (columns D&E), select cells in column A (from A2 down to that row with data) and merge cells. Its just some further formatting whihc I would like to add to tidy things up but its proving a bit tricky for me. Any ideas

Thanks guys

 

[Lewiy]

Which cells do you want to merge together?

 

[jose001]

From Cell A2 down to the last cell in A which has data in the row, the data will be in dolumns D&E though

 

[Lewiy]

Try this:

Sub MergeCells()
Dim LimitD As Long
Dim LimitE As Long
LimitD = Cells(Rows.Count, 4).End(xlUp).Row
LimitE = Cells(Rows.Count, 5).End(xlUp).Row
If LimitE > LimitD Then LimitD = LimitE
Range("A2:A" & LimitD).MergeCells = True
End Sub

 

[jose001]

Thats fantastic Lewiy, thanks very much. Can you recommend a good book on VBA, the learning curve is very steep with trial and error but lots of fun though!!

 

[jose001]

Thats fantastic Lewiy, thanks very much. Can you recommend a good book on VBA, the learning curve is very steep with trial and error but lots of fun though!!

 

[Lewiy]

You could check out MrExcel's VBA book:
http://www.mrexcel.com/sunshop/index.php?action=item&substart=0&id=49

But also, this board is an amazing tool in itself, it's given me everything I've ever learned about VBA!