how to grab all the cells in header row, even blank ones in between

[question610]

How do i get the cells for the header row with blanks? There are some blanks in between columns but I want to grab the blanks as well...The thing is I don't want to grab the trailing blanks at the end.

I have this

Set HeaderRange = Dataws.Rows(HeaderRow).SpecialCells(xlCellTypeConstants)

So...
Col:
A B C D E F G H I J K
1 <blank> 3 2 <blank> 5

I want it to get A-F and ignore G on...

 

[Fluff]

Your example isn't very clear, but does this do what you want

Dim UsdCols As Integer
UsdCols = cells(headerrow, Columns.Count).End(xlToLeft).Column
With DataWs
    Set headerrange = .Range(.cells(headerrow, 1), .cells(headerrow, UsdCols))
End With

 

[question610]

Hi,

Thank you, it works for the most part.

This is my code

Dim UsdCols As IntegerUsdCols = Cells(headerrow, Columns.Count).End(xlToLeft).Column
With Dataws
    Set HeaderRange = .Range(.Cells(headerrow, 1), .Cells(headerrow, UsdCols))
    HeaderRange.Select
End With
HeaderRange.Select
With HeaderRange
    lastrangecol = .Columns(.Columns.Count).Column - .Columns(1).Column + 1
End With
ActiveCell.Offset(1).EntireRow.Insert


For Each Cl In HeaderRange
    Cl.Offset(1, 0).FormulaR1C1 = "=CELL(""col"",R[-1]C)"
Next Cl

The work is being done in the loop through Header Range. I am trying to go through each cell in the range and underneath it, insert a new row and it will have that column number. However, if the last cell is merged then, my =cell("col",r[-1]c) formula will not pick up. For example, if the last merged cell is 25-28 then header row only goes to 25 and thus the row below it will be 25 but I want 26-28 as well.

Your example isn't very clear, but does this do what you want

Dim UsdCols As Integer
UsdCols = cells(headerrow, Columns.Count).End(xlToLeft).Column
With DataWs
    Set headerrange = .Range(.cells(headerrow, 1), .cells(headerrow, UsdCols))
End With

 

[Fluff]

Merged cells are a nightmare & as such I avoid them like the plague.
You say

For example, if the last merged cell is 25-28

Is there any data in an UNMERGED cell that is in Column 28?

 

[question610]

Merged cells are a nightmare & as such I avoid them like the plague.
You sayIs there any data in an UNMERGED cell that is in Column 28?

There is 3 separate columns of information underneath that last merged cell.

For example: the information is like this
25 26 27
Row 1 | MERGED CELL |
Row 2 | A | B| |C|


The expected results are
25 26 27 Row 1 | MERGED CELL |
25 26 27 ("=CELL(""col"",R[-1]C)")
Row 2 | A | B| |C|

but I only get

25 26 27 Row 1 | MERGED CELL |
25 ("=CELL(""col"",R[-1]C)")
Row 2 | A | B| |C|

This is bc I'm looping through the header range and I guess since it's a merged cell, it treats 25 as the last cell in the header range.

 

[Fluff]

Try this instead

Dim UsdCols As Integer

UsdCols = cells.Find("*", after:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
With DataWs
    Set headerrange = .Range(.cells(headerrow, 1), .cells(headerrow, UsdCols))
End With

 

[question610]

This is great! it works! Could you explain what you did and how you went about trouble shooting it? Also, what does usd mean?

 

[Fluff]

UsdCols doesn't actually mean anything, it's just my way of saying Used columns.
In my original code

UsdCols = cells(headerrow, Columns.Count).End(xlToLeft).Column

goes to the very last column in the sheet & then moves left until it finds a cell in that row with a value, which, in this case, didn't work because of the merged cell.
Whereas

UsdCols = cells.Find("*", after:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

looks for the last column with a value regardless of which row it's in
HTH

This is great! it works!

Glad to help & thanks for the feedback

 

[question610]

How do I do the same thing, capturing all the cells even blank ones in between starting from Row 2 of Column A, B, C?

 

[Fluff]

Not quite sure what you mean.