Some VBA code for a Button to count the number of VISIBLE rows, and print those numbers in column B in the rows, but...

Question

(Note for those who already read this, in error I used the word Table earlier, that was wrong, its only a group of cells with borders

that looks like a table, sorry, Ive tried to correct that below by referring to that as a group of rows)

Task sounds simple, but I'm just not getting it.

I created this Excel file that others in my department will be using. Its pre-populated with content that 'looks' like a table, but is not.

its just cells with borders, and each row of content is numbered in Column B.

The Users in my department will be hiding and adding rows as needed that already are numbered, so the number column has to handle being Hidden, not Deleted, they may want to UnHide them as needed, so the effort has to recognize hidden rows and then pick up where the last Visible row left off. similar to =SUBTOTAL(102, which skips counting hidden rows, but dont be constrained by using that formula.

I have a long list of items in a range of rows, one per line, which are already numbered,

I have the rows already numbered that matches the VISIBLE rows in about 50 lines.

(by numbered I mean based on which way to go with this, they are actual numbers,

or formulas to dynamically put the next number in place in case any rows get HIDDEN,

like using =AGGREGATE(2,5,B$2:B6)+1 or =SUBTOTAL(102, .....)

Either way is fine with me, and works, until I "Insert a copied row", or adding a row, and the copied row

will not update the AGGREGATE code which has become the problem.

The users using this wont know code or formulas, so I cant expect them to fix it or update it just because they add or copy a row.

This worked great when all they did was click one of my buttons that hid any row they didnt want, its just when a row was Inserted copied row, or added, since the user wont know how to add code to that new row.

There will be at times a row(s) that gets hidden on purpose by the user, not deleted, in this group of rows, and all works fine.

Its just the Insert Copied Row, or them Adding a row, that is my problem.

If I dont use any formula, and the numbers are just Hard entered, 1,2,3,4... I know I can just

select the top 2 numbers and double-click the bottom-right small green square and all the numbers will

run down and be correct.

I know that will work, but Im creating this for non-excel type people who just want to hit a button and let that do it for them.

I didnt need a Button for this until the Insert Copied Cell question was requested, and I still wont need a Button if anyone knows a way

for the number column to auto-renumber by itself based on Hide or Insert Copied cell.

I already have a button and a Macro created for this, but cant find the right combination of code to make it work, when the inserted copied code is used.

Ive tried to use a Counter for items in the description rows that would tell me how many rows there are THAT IS NOT HIDDEN, (AGGREGATE and SUBTOTAL) and use that as a variable to place in a RANGE to re-number the VISIBLE rows, but lack the knowledge how to place that variable in the second part of the range thats always changing, base on if the user has hidden or inserted any rows in this list.

I recorded a Macro to see how Excel handles the 'Select the first 2 rows and click on that bottom-right item to renumber',

and it works, but the range is fixed for that situation, ("B3:B54"), and when a row(s) gets added, I dont know how to increment that last

number to match the new number of rows in my group of rows to renumber.

' Range("B3:B4").Select
' Selection.AutoFill Destination:=Range("B3:B54")
' Range("B3:B54").Select

Ive replaced B54 with the counter variable, but seems to be a mis-match of code and it didnt like that.

Watched several videos, but cant find the right situation. Been several days trying to find a solution.

Tried this too, but a mis-match type error popped-up:

' Selection.AutoFill Destination:=Range("B" & BeginCount & ":B" & TotalRowsToPrint)

If there is a simpler way to approach this, I'll take it.

Anyone with ideas please? Thanks.

Answer 1

BrainStain00,

So you want to number column B not column A.

And the start is in row 3 not row 2.

Edit the named range GroupedRows so that the referenced range is:

=OFFSET(InstallationChklst!$B$2:$F$2,1,0,COUNTA(InstallationChklst!$C:$C)-1)

Put this code in the module of sheet InstallationChklst.

Only then the code will fire when a cell in column B (now not A as before) is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Set rng = ThisWorkbook.Sheets("InstallationChklst").Range("GroupedRows").Columns(1)

    If Not Intersect(Target, rng) Is Nothing Then

        Application.EnableEvents = False

        rng.Formula = "=SubTotal(103,$C$3:C3)"

        Application.EnableEvents = True

    End If

End Sub

Change something in the first column of GroupedRows and that column will be filled with the right formula to number the rows.

I’ve assumed that column C from GroupedRows has a value in every cell (so there is no empty cell in that range) otherwise the numbering is not right.

Jan

Answer 2

BrainStain00,

When you use this formula in the first column of your table (starting in A2): =SubTotal(3,$B$2:B2)

then this code perhaps will do the renumbering (without a button):

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim rng As Range 

    Set rng = ThisWorkbook.Sheets("Blad1").ListObjects("Tabel1").ListColumns(1).DataBodyRange 

    If Not Intersect(Target, rng) Is Nothing Then 

        Application.EnableEvents = False 

        rng.Formula = "=SubTotal(3,$B$2:B2)" 

        Application.EnableEvents = True 

    End If 

End Sub 

(I assumed you have indeed a table)

Jan

Answer 3

Thanks Jan,

I could have confused the issue, and will correct this up in my original post.

When I say Table, it turns into a table once I convert it to word, but in Excel, it is just Borders I add to represent

a table-look which turns into a table once in Word. (long story there on the conversion process. I start in Excel so

the user, employees I work with, can hide tabs not used in their final Word document, that way they can have a

custom document every time if wanted).

I did place your formula in A2, (looked similar to one I had tried earlier, =SUBTOTAL(102,A$2:A3),

I used 102 since that tells SUBTOTAL to skip counting on Hidden rows, but Im guessing your Macro

may take care of that.

I wasnt able to fully test this for hidden rows the user may hide, or add, would your macro take care of this?

I only slightly familiar with naming, so is Blad1 the name I give the sheet(tab)? and Tabel1 the name I give to the Table (if I had one)?

I did rename my sheet(tab) to that and it crashed at that line, probably since I didnt have a table with that name. Sorry for causing you to

spend time on me calling it a table.

It looks like the Macro is a living code that does not need to be run every time that file/sheet gets opened, its live all the time? I hadnt know that before, if so thats good to know, not needing a button, thanks.

Thanks if you want to try this again, sorry for making people think its a table Im using, that would cause confusion, I will correct that now.

Answer 4

BrainStain00,

(The name of the sheet now is Sheet1)

Instead of having a table (would be easier I think) I now use a dynamic range and name it GroupedRows.

You can do that in menu Formulas>Define name

In name write: GroupedRows

In references to (?) write this formula: =OFFSET(Sheet1!$A$1:$F$1,1,0,COUNTA(Sheet1!$A:$A)-1)

Then this is the new VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("GroupedRows").Columns(1)

    If Not Intersect(Target, rng) Is Nothing Then

        Application.EnableEvents = False

        rng.Formula = "=SubTotal(103,$B$2:B2)"

        Application.EnableEvents = True

    End If

End Sub

Each time something changes in the first column of the range GroupedRows the counting formula ("=SubTotal(103,$B$2:B2)")  will be renewed in that column.

Jan

Answer 5

Hi Jan,

This seems to be a good working plan, except for one thing...

First let me make sure Ive done what was expected.

I created a named group called GroupedRows

I copied your formula that you listed,
=OFFSET(Sheet1!$A$1:$F$1,1,0,COUNTA(Sheet1!$A:$A)-1)
and replaced Sheet1 with the Tab name I had in my workbook,
I currently have about 30 tabs in it, so I now have...
=OFFSET(InstallationChklst!$A$1:$F$1,1,0,COUNTA(InstallationChklst!$A:$A)-1)

I hope this is the right step.

Next I copy and pasted the macro into a Module. Should it have been in that macro page
where I have other Macros or just in a Module?

Next I edited the macro to replace the tab name in place of Sheet1, is that correct?
Set rng = ThisWorkbook.Sheets("InstallationChklst").Range("GroupedRows").Columns(1)

Was that ok to do?

Then copied the formula you provide into A2
=SubTotal(103,$B$2:B2)

Im not sure where to have pasted that, but your original comment said to paste it there,
but I dont know in this case if that was correct?

You might have seen I updated my original post, and included an image of how the end user
sees it.
Im guessing that I can hide column A just to prevent distraction if A2 was where I was to
paste and extend it on down, and just let my Print Area in Excel to never see that column.

The problem Im having, (at least the way I interpreted you code you kindly sent,
is that column A is the one that is being updated, and displaying correctly,
even when I insert a copied cell(row), or hide a row, and column B is not updating, which is part of
the rows and contents.(which I wrongly called a table)

Its column B that I was hoping to update, and I think thats what you intended?

Was I supposed to copy the formula to Col B since the one with the formula is the one updating?

Did I insert or copy something wrong or in the wrong place?

Ideas?

Thanks Jan for all that code, I would have never thought of all that!