Counta with changing range...no empty cells

JEHalm · Sep 19, 2011

Hello, I was having a problem yesterday when I tried to use VBA to counta all cells (no empties). Every time I download to Excel I will have different quatities of Rows...somewhere between 9,500 and 11,000 Rows. I am using the Range Selection up to select all cells above.

I would like to use the first open cell to counta all cells above and insert the amount of cells in the first open cell at the bottom... lets say row 10,500.

Please advise

JH

Luke M · Sep 19, 2011

Here's a snippet of code to get the row number for first blank line. Is this what you want?

[pre]

Code:

NewRow = Range(&#34;A65536&#34;).End(xlUp).Offset(1, 0).Row
Or, if you just wanted the Counta:

xCounta = WorksheetFunction.CountA(Range(&#34;A1:A&#34; & NewRow))

[/pre]

JEHalm · Sep 19, 2011

Hi Luke...please see the partial spreadsheet and code.

RWA Type RWA Number RWA Number Org Code

N 1156597 N1156597 P0525172

N 3577941 N3577941 P0573000

N 3063934 N3063934 P0525223

N 2842185 N2842185 P0525223

N 3664812 N3664812 P0574000

A 2835406 A2835406 P0520001

N 2615765 N2615765 P0525253

This is a table that changes the total number of rows every week as some of the projects (RWA Numbers) are closed and new ones are added. Therefore, the total number of populated are always changing each week...the # of columns will not vary.

I used a loop and concatenate function to merge the strings from columns A and B. I insert a row column c and run the concatenate formula. This fills in every blank cell in column C with the merged strings. At the end I select the first empty cell at the bottom of column C. I'm trying to use some counta code to count all of the cells above, with the exception of Cell C1. Here is the actual code from my sub. Again, I want to see the total count(a) of the rows in the first empty cell at the end of the table (or, an offset down lower). Your first answer maybe be correct but may I am not using it correctly? Or, do the concatenated cells need to be copied and saved as values?? Your help is deeply appreciated. I'm very happy with my progress but I am stumbling on this.

Sub Overobligated()

Dim i As Integer

Dim LastRow As Long

Range("A1").Offset(1, 0).Select

Range("C:C").Insert Shift:=xlToLeft

Cells.Range("C1") = ("RWA Number")

Range("C1").Offset(1, 0).Select

'Range(Selection, Selection.End(xlDown)).Select

With Selection.Font

.Name = "Arial"

.FontStyle = 0

.Size = 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.ThemeColor = xlThemeColorLight1

Range("c1:c20000").NumberFormat = "General"

'Selection.End(xlDown).Select

'ActiveCell.Offset(-1, 0).Select

'With Selection.Borders(xlEdgeBottom)

'.LineStyle = xlContinuous

'.Weight = xlMedium

End With

For i = 1 To Selection.CurrentRegion.Rows.Count - 1

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"

ActiveCell.Offset(1, 0).Select

Next i

End Sub

' Cells(Rows.Count, 1).End(xlUp).Select

' Range("C1").Offset(1, 0).Select

' Selection.End(xlDown).Select

' ActiveCell.Offset(1, 0).Select

The RWA column was populated with a concantenate code.

Luke M · Sep 19, 2011

Re-write of end of code:

[pre]

Code:

Dim xRow As Integer
xRow = Selection.CurrentRegion.Rows.Count - 1 &#39;Why the -1?

&#39;Sets formula all in one shot, no need for loop
Range(Cells(2, &#34;C&#34;), Cells(xRow, &#34;C&#34;)).FormulaR1C1 = _
&#34;=CONCATENATE(RC[-2],RC[-1])&#34;
Cells(xRow + 1, &#34;C&#34;).Value = _
WorksheetFunction.CountIf(Range(Cells(2, &#34;C&#34;), Cells(xRow, &#34;C&#34;)), &#34;&#60;&#62;&#34;)

[/pre]
Does this work okay?

JEHalm · Sep 19, 2011

So Sorry Luke. I'm afraid I may be flustering you. Maybe I can explain it better. My data set is comprised of approximately 48 colums and 10,500 rows. Columns A and B are essentially small project construction projects. Column A indicates the type of construction (A, B, C, F, N, etc.). Column B has the project number. None are the same.

I inserted a blank column in column C...shoving the rest of the columns offset 1 column (i.e., (0,1)). Within the empty Column C I inserted a loop and a formula to concatenate columns A and B. All the way down the bottom of the 10,000+ rows.

Now, I wish to COUNTa and display the total of all the cells in column C...giving me the total number of open projects.

Your most recent reply used the concatenate formula which I was able to get working yesterday. However, I have been unable to produce code that will count the occupied rows above and display the count in the first empty cell at the bottom of the column.

I call this range as being variable in that every week the number of projects fluctuate from week to week.

HERE IS THE CODE AGAIN

' This part works - CONCATENATE

For i = 1 To Selection.CurrentRegion.Rows.Count - 1

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"

ActiveCell.Offset(1, 0).Select

Next i

End Sub

'NEED CODE FOR COUNTA RIGHT AFTER THE CONCATENATE FORMULA LOOP AVE

' Do I need some type of break here between the concatenate and the counta portions. Like End For, etc.

'THE CODES BELOW DID NOT WORK

'Sets formula all in one shot, no need for loop

'Range(Cells(2, "C"), Cells(xrow, "C")).FormulaR1C1 = _

' "=Counta(RC[-2],RC[-1])"

'Cells(xrow + 1, "C").Value = _

WorksheetFunction.CountIf(Range(Cells(2, "C"), Cells(xrow, "C")), "<>")

' Cells(Rows.Count, 1).End(xlUp).Select

' Range("C1").Offset(1, 0).Select

' Selection.End(xlDown).Select

' ActiveCell.Offset(1, 0).Select

' Range(Selection, Selection.End(xlDown)).Select

'Range(Selection, Selection.End(xlUp)).Select

'ActiveCell.Formula = "=counta "

'ActiveCell.FormulaR1C1 = "=COUNTA(Range(Selection, Selection.End(xlDown)).SelectSelect"

'This loop runs as long as there is something in the next column

'Do While Not IsEmpty(ActiveCell.Offset(0, -1))

'ActiveCell.Formula = "=CONCATENATE(a2, b2)"

'ActiveCell.Offset(1, 0).Select

'Loop

'i = "B2"

'If "i" > "" Then

'ActiveCell.Formula = "=CONCATENATE(a2,b2)"

'Else

'End If

Also, when I concatenate the cells in columns A and B do I have to cut and paste values??

Luke M · Sep 19, 2011

JEHalm,

No, you don't need to paste as values. What I attempted to convey (poorly) in my previous post was that you don't need to take so many steps in your code. The fact that you can figure out how many rows to look at dynamically, which you were using in your loop, lets you get the record count and the range size you need to concatenate. This code:

[pre]

Code:

&#39;How many rows are there?
Dim xRow As Integer
xRow = Range(&#34;A65536&#34;).End(xlUp).Row
&#39;Concatenate our values
Range(Cells(2, &#34;C&#34;), Cells(xRow, &#34;C&#34;)).FormulaR1C1 = _
&#34;=CONCATENATE(RC[-2],RC[-1])&#34;
&#39;We have already counted the cells, so we can use this for record count
Cells(xRow + 1, &#34;C&#34;).Value = xRow - 1

[/pre]
does just that. We figure out how many rows there are (which can change, hence why we start at row 65536 and go up). Let's say this is 100. Then we put a formula in the range C2:C100. Since we know that last row was 100 you don't want to count the header row, there are 100-1 records, aka 99.

If this doesn't work, can you elaborate on what doesn't work? (code doesn't run, code stops, unexpected results)

JEHalm · Sep 19, 2011

Wow, that worked wonderfully. That will be extremely valuable down the road. Thanks so much for your knowledge and patience.

JH (J-dubs)

JEHalm · Nov 27, 2011

Luke, how can I edit the code in your message above to sum a column (say column i) rather than the count function (which worked perfectly)?

JEHalm · Dec 1, 2011

Please advise

JEHalm · Dec 3, 2011

OK, am I banned or something??

Counta with changing range...no empty cells

JEHalm

New Member

Luke M

Excel Ninja

JEHalm

New Member

Luke M

Excel Ninja

JEHalm

New Member

Luke M

Excel Ninja

JEHalm

New Member

JEHalm

New Member

JEHalm

New Member

JEHalm

New Member