Help to auto extend formula when new column is added daily

[vkingxl]

I have a sheet called "A-LOG" and every day I use an excel VBA macro to take a snapshot of column A which has live alarm data, and pastes a copy of column A after the last column on the sheet with data in it. Over time I have a good indicator of daily alarms history. I also use a simple formula in column B to count how many alarms are found across each row's range of columns.
Existing formula: =COUNTIF(B4:BZJ4,"?*")

The problem is every few months or so I need to keep manually changing the last column text to expand the formula. I have the following VBA in my macro to find the last column in use and I added the bold text to now also paste that numeric value into the A-LOG sheet.

LastColumn = Sheets("A-LOG").Cells(1, Sheets("A-LOG").Columns.Count).End(xlToLeft).Column
Sheets("A-LOG").Range("Z1") = LastColumn

Then I found this formula that converts that numeric value into the text column name equivalent so I put that in Z2
Z2=SUBSTITUTE(ADDRESS(1, Z1, 4), "1", "")

Then I was thinking I could use INDIRECT to use in my count formula like:
=COUNTIF(B4:INDIRECT(Z$2&4),"?*")
Which does supply the correct count, but I can't copy this formula down the page as the 4 number doesn't change.

Maybe there is a more efficient solution or formula that could be used, but I do have about 15k rows so I do want to be mindful of calculation stress on the workbook.

 

[Fluff]

If the formula is in col B, how about

=COUNTIFS(C4:INDEX(4:4,,$Z$1),"?*")

 

[Peter_SSs]

You do have a large calculation range so the calculation is fairly onerous. However, my test worksheet (15,000 rows x 2,000+ columns) shows no extra calculation time for a countifs formula if you simply use the full available range without worrying about Z1:Z2

=COUNTIFS(C4:XFD4,"?*")

And without knowing what your data is actually like, would this do the same job as it is considerably faster with my test data?

=COUNTA(C4:XFD4)

 

[vkingxl]

Awesome - all 3 of those answers solved my question. I also was working with this answer when your responses came through.
=COUNTA(A4:INDEX(4:4, 1, Z$1))

Since the =COUNTA(C4:XFD4) is so simple and fast I would have used that one, but I did have another calculation in another column counting back 15 days to show how many times the unit was alarmed. So knowing the last column # in Z1 is most beneficial in my case.

Thank you both very much!!

 

[Fluff]

Glad we could help & thanks for the feedback.