We help IT Professionals succeed at work.

Excel VBA - Determine Number of Columns in a Named Range

I am trying to write a routine that takes a named range, rInput, which is made of merged cells spanning an unknown number of columns, but always in only a single row.

The purpose of the routine is to make the row height of "rInput" adjust to fit the text in "rInput" so that all text is visible regardless of how many lines it takes up inside of "rInput".

The routine will do the following:
1) unmerge "rInput"
2) determine how many columns were unmerged
3) determine the width of each column
4) make the width of the first cell equal the sum of all the widths so that the first cell now takes up the same width as "rInput" did originally
5) make text in first cell wrap, left align, top align, and AUTO-FIT ROW HEIGHT --this is the row height we'll want for the reconstituted merged range
6) resize column width of first cell to its original width --but keep row height unchanged from step 5 above
7) merge cells again keeping text wrap and alignment  --now we have the original merged range "rInput" but with the appropriate row height to fit all the text in it.

I think I can write all the code for this if I could only figure out how to determine the number of columns in rInput.  Is there a way to do this?
Watch Question

NorieAnalyst Assistant

Wouldn't it just be Range("rInput").Columns.Count, after 'demerging' of course.

Or do you want to find the no of columns while there are still merged areas in the range?


imnorie:  no, it won't work because once you unmerge, rInput is the first cell only so Range("rInput").Columns.Count will return "1".  All the other cells of the unmerged input do not have a named range.  They are, however, part of the selected cells.  There must be a way to find the column number of the last cell in the selected (and now un-named) range.
NorieAnalyst Assistant

Don't unmerge the range then.
Top Expert 2011

Try this:

Sub test()
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet
x = ws.Range("ringput").Columns.Count
For i = 1 to x
'add the code you want here
End Sub

Open in new window


Unmerging won't work either.  This is because the way the merged ranges were created was by first naming a single cell, then merging with cells to the right.
This creates a situation where range("rInput").Columns.Count returns "1" regardless of whether the range is unmerged or not.
Short of renaming all the ranges, is there a way to count the columns of a selection of cells?  This is what we essentially have when unmerging a range that was created by merging a named cell with a bunch of unnamed cells.
Analyst Assistant
Merging the named range with other columns/cells doesn't change the named range.

So if it had one column when created it still only has one column after being included in a merge.

To get the no of columns of a selection use Selection.Columns.Count.
Top Expert 2011

Look at my code...you first read the number of columns...assign it to a variable...then you can use it after you UNMERGE...
NorieAnalyst Assistant


The no of columns before and after umerging is the same - the named range never changes.


imnorie, "selection.columns.count" is exactly what I needed.  Thanks!

xtermie, thanks for your input but range("rInput").columns.count doesn't work because the original named range is only one cell which was then merged with others, so range("rInput").columns.count returns "1"