Counting certain cells in a row on excel macro

[spiffmonkey1]

Hi,

I want to count certain cells in a column that have format for example border 1x thick or etc. I've been trying to use ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas) but I don't know how to specify "UsedRange" to the range of the row that I want. This function is suppose to return a value or object but it doesn't work on my macro where the variable equals the formula above.

Thanks

 

[Weaver]

You could use

activesheet.rows(10).specialcells(xlcelltypeformulas)

but I don't think that will help you count cells with a certain border format.

 

[spiffmonkey1]

Didn't work :/
Basically I'm doing this because I want to make a macro that goes from a column number to the end of the last column(that has text or some format) so that the macro will not apply to empty columns. The macro only applies to one row. I want the variable to go from a column number(which I know how do) to the cell count of the number of cells in the row with anything but blank.

Thanks again

 

[Weaver]

So you want to know the column number of the last cell with data in it in a given row?

lastCol = cells(myRow,columns.count).end(xltoleft).column.

ought to do this.

or if you want the range:

set myRange = range(cells(myRow,startCol), cells(myRow,columns.count).end(xltoleft))

 

[spiffmonkey1]

I tried using Count,CountA, and Specialcells...none worked
Maybe I can do Selection.Count and play with that?

 

[spiffmonkey1]

So you want to know the column number of the last cell with data in it in a given row?

lastCol = cells(myRow,columns.count).end(xltoleft).column.

ought to do this.

or if you want the range:

set myRange = range(cells(myRow,startCol), cells(myRow,columns.count).end(xltoleft))

Awesome works perfectly!! Your a genius! But can you expain what .end(xltoleft) does? Thanks!!

 

[spiffmonkey1]

I also want you know if we can use the method above but instead count the last column that has a format like color or border.
Thanks

 

[spiffmonkey1]

I'm trying to use this-
FinalColumn = Range(Cells(70, "D"), Cells(70, Columns.Count).End(xlToRight)).Column
Because I want to get the count cell BEFORE the blank cell in the row
However, i don't know why its not working
I also tried-
FinalColumn = Range(Cells(70, "D"), Cells(70, Columns.Count).End(xlToRight)).Row
But I have to add numbers to FinalColumn to make it fit the count cell of the column before the blank cell

 

[spiffmonkey1]

Fixed it by doing-
FinalColumn = Range(Cells(70, "A"), Cells(70, "AIU").End(xlToLeft)).Count
Setting the range from A to basically infinity instead of using columns.count

 

[Weaver]

Sorry I didn't get back to you sooner - looks like you figured it out for yourself.

For the record, and you probably aready figured this, but end(xltoleft) is the equivalent of putting your cursor in a cell, holding ctrl and pressing the left arrow. So using columns.count means you're starting from the rightmost column.