Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Looping through Merged cells in VBA

Tags:

excel

vba

Is it possible to loop through merged cells in vba.

  • I have 6 merged cells in the range B4:B40
  • I need the values in these 6 cells 6 iterations only.
like image 965
Sarika.S Avatar asked Feb 22 '12 08:02

Sarika.S


People also ask

Does VBA work with merged cells?

In VBA, there is a “MERGE” method that you can use to merge a range of cells or even multiple ranges into one. This method has an argument “Across” which is optional. If you specify TRUE it will merge each row in the range separately, and if you specify FALSE it will merge the entire range as one.

How do you reference a cell that is merged?

Right-click the merged cell B1:D1, select "paste special -> formulas" You should see the merged cell being 0. Type Col1 in the merged cell. You should now see all B2, C2, D2 to be Col1, i.e. now you can reference the merged cell as you expect it to be.

Can you Vlookup a merged cell?

The Vlookup function will work in merged cells as usual in Google Sheets, but the output won't be what you are seeking. What's the solution to it? For example, if an item has three prices based on its grade, we may enter such data as below. Item: Mango (A1:A3 merged and entered it).


1 Answers

The above answers look to have you sorted.

If you don't know where the merged cells are then you can use the following routine to quickly detect them.

When I built Mappit! I realised that when I developed merged cell reporting that merged cells were part of xlBlanks

So you can use the code to detect merged cells immediately rather than loop through each cell testing for the MergedCells property being true.

Sub DetectMerged()
Dim rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng1 = Intersect(Cells.SpecialCells(xlFormulas), Cells.SpecialCells(xlBlanks))
Set rng2 = Intersect(Cells.SpecialCells(xlConstants), Cells.SpecialCells(xlBlanks))
On Error GoTo 0
If Not rng1 Is Nothing Then MsgBox "Merged formulae cells in " & rng1.Address(0, 0)
If Not rng2 Is Nothing Then MsgBox "Merged constant cells in " & rng2.Address(0, 0)
End Sub
like image 99
brettdj Avatar answered Sep 29 '22 01:09

brettdj