Counting Records/Rows

Question

I have a macro that saves an excel file down to .txt. I need to insert a record count to the top of the .txt file ("Records|123"). I have the row inserted, I have the word Records in A1, now I'm trying to figure out how to get a row count in B1. I've tried CountIf, LastRow, EndRow and now COUNT. I'm subtracting 2 because row 1 is the record count and row 2 is column headers, and I'm referencing column D because columns A-C contain numbers and it didn't like that either. Here is what I have:

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Records"
Range("B1").Select
ActiveCell.Value = (EndRow - 2)
ActiveCell.FormulaR1C1 = "=COUNT(D:D)"

This is giving me Records|0. Does anyone have any ideas on how to get this to work? Or can you point me in a different direction? Thank you in advance.

COUNT gives the total cells containing numbers. Perhaps you want COUNTA? — SJR, Feb 16 '18 at 17:29
[Avoid using `.Select`](https://stackoverflow.com/q/10714251) — Mathieu Guindon, Feb 16 '18 at 17:53

score 3 · Accepted Answer · answered Feb 16 '18 at 17:37

3

Something like this.

Dim ws As Worksheet
Dim lr As Long

ws = ThisWorkbook.Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

With ws
    .Range("B1") = lr - 2
End with

answered Feb 16 '18 at 17:37

GMalc

2,608
1
9
16

score 0 · Answer 2 · answered Feb 16 '18 at 17:35

You could have just gotten a row count before you inserted the header row.

dim newvariable as long
newvariable = Thisworkbook.worksheets("yoursheet").UsedRange.Rows.Count

And then insert your new header row and place that value in the cell like:

Thisworkbook.worksheets("yoursheet").Range("a1").Value = newvariable

Counting Records/Rows

2 Answers2