Dynamic count rows down?

[Wolfspyda]

So as a base Excel Magic Tric 917 (it was a long time ago), did a creative way of counting the rows from 1 to “x” and then repeat itself.

Using that same logic I have tried to reverse the count function so it starts at 10 and counts down to 1 and then repeats.

The count function on MT917 was:-

Rows(C$2:C2) and as it expanded it would count the Rows.

The function I’ve tried is this:-

Rows(C2:C$10) this results in 9 and counts down to 1. The issue I have found is that this 2nd part of the Rows count is fixed as the maximum and isn’t variable. It counts down correctly as it’s locked, but I’m wondering if there is a cleaner way to go about this or a formula I haven’t considered.

Essentially I’m trying to swap the repeating function to count down from “X” to 1 then start at “X” again.

 

[Skyybot]

This will count down from maximum rows (dynamic) to Row 2 in Column "C" and print the value of each cell in the Immediate Window of the VBA Project Model. Change the Workbooks and Sheets name to match your Workbook. I'm assuming that you are using Office 365 on Windows. You really should think about updating your User Profile to show your Office version and Platform so that you can get better help.

Option Explicit

Sub CountDownRows()
Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Integer, i As Integer
Set wb = Workbooks("Book1"): Set sht = wb.Sheets("Sheet1")
lRow = sht.UsedRange.Rows.Count: Set rng = sht.Range(Cells(2, 3), Cells(lRow, 3))
For i = lRow To 2 Step -1
    Debug.Print sht.Cells(i, 3)
Next i
End Sub

 

[Scott R]

with Office 365:

=LET( reps, 25, start, 10,     MOD( SEQUENCE( reps * start,, start, -1) - 1, start ) + 1 )

 

[Wolfspyda]

This will count down from maximum rows (dynamic) to Row 2 in Column "C" and print the value of each cell in the Immediate Window of the VBA Project Model. Change the Workbooks and Sheets name to match your Workbook. I'm assuming that you are using Office 365 on Windows. You really should think about updating your User Profile to show your Office version and Platform so that you can get better help.

Option Explicit

Sub CountDownRows()
Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Integer, i As Integer
Set wb = Workbooks("Book1"): Set sht = wb.Sheets("Sheet1")
lRow = sht.UsedRange.Rows.Count: Set rng = sht.Range(Cells(2, 3), Cells(lRow, 3))
For i = lRow To 2 Step -1
    Debug.Print sht.Cells(i, 3)
Next i
End Sub

Thanks for that.

Yes I’m using 365, not sure how to add that to my profile but I’ll look into it.

I was trying to avoid using VBA, as the sheet needs to be Mac compatible

 

[Skyybot]

Ah yes. I know nothing of Mac. I prefer to use Microsoft Excel in a Microsoft environment. Sorry. There are Mac users on here that can help, for sure. So, to update your profile click on your account icon (upper right) and then click on account details.

 

[Fluff]

Yes I’m using 365, not sure how to add that to my profile

you can click on this link Account details (or click your user name at the top right of the forum) (Don’t forget to scroll down & ‘Save’)