PAfE API: RefreshSheet

Post Reply
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

The API for PAfE has the procedure RefreshSheet(), which behaves as the documentation states:
RefreshSheet refreshes IBM Planning Analytics for Microsoft Excel data in the active sheet.
I'm curious if there is a way to refresh a specific worksheet. I want to use VBA to update hidden worksheets but not necessarily the active worksheet.

For example: I have a Quick Report as the active worksheet but I want to update a TM1ELLIST function on another worksheet that is hidden. If I execute the RefreshSheet() function then the API updates the Quick Report along with the TM1ELLIST on the hidden sheet.

I need to selectively update data. Is this possible?

Seems like I should be able to drill down on some property of the Reporting object to get the specific report worksheet I want to refresh but I can't find anything in the API documentation.
War teaches us geography, getting old teaches us biology.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAfE API: RefreshSheet

Post by Wim Gielis »

Can you .Activate the sheet, refresh and .Activate back the original sheet ?
It's not the nicest solution but might probably work. I haven't tested it.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAfE API: RefreshSheet

Post by gtonkin »

You could try go through the report collection using GetReports()

Have a look at this thread
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

Thanks, Wim & GTonkin, for your replies! (Sorry in advance for the TLDR response)
Wim Gielis wrote: Mon Apr 11, 2022 8:14 pm Can you .Activate the sheet, refresh and .Activate back the original sheet ?
It's not the nicest solution but might probably work. I haven't tested it.
I attempted the Worksheet.Activate solution but my results were erratic. Sometimes it updated the target worksheet ONLY but other times it would update the Quick Report unexpectedly. I made certain I had Application.EnableEvents set to TRUE but maybe I need to insert a "sleep" function there to allow the Cognos API to catch up with what's happening in the VBA.
gtonkin wrote: Tue Apr 12, 2022 4:55 am You could try go through the report collection using GetReports()

Have a look at this thread
This is EXACTLY the sort of thing I was looking for. That thread mentioned using:

Code: Select all

Reporting.QuickReports.Get(qrIDNumber).Commit
I'm curious if there is something like:

Code: Select all

Reporting.QuickReports.Get(qrIDNumber).Refresh
BUT… maybe I'm thinking about this entirely side-wonked.

The worksheet I'm trying to update doesn't have any report objects on it. It just has TM1ELLIST. There's no specific report to refresh, it just needs to update the results of that function for that particular range. The steps in the code are like this:
  1. Update the MDX
  2. Turn off calculation and screen updating
  3. Clear out range where values existed before
  4. Throw down TM1ELLIST at top of range with updated MDX
  5. Calculate that worksheet ONLY, also includes calls to Cognos API for Reporting.RefreshSheet
  6. Translate results of TM1ELLIST to values
  7. Turn on calc/screen updating
If I don't include the API call for RefreshSheet, the TM1ELLIST just returns "RECALC_0_0" which is a placeholder the function uses while the API is fetching data.

Is there a way to force TM1 functions to refresh? Or should I go about this differently?
War teaches us geography, getting old teaches us biology.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAfE API: RefreshSheet

Post by gtonkin »

Have you tried calling RefreshSheet from the sheet with the TM1ELLIST? Sounds like it but just confirming.

I do remember having an issue where objects did not refresh as it did not see the sheet as dirty.
I *think* I may have dirtied the sheet through VBA.

Try dirty the sheet manually then run the Macro with the RefreshSheet and see if you get a better result.
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

gtonkin wrote: Wed Apr 13, 2022 5:31 am Have you tried calling RefreshSheet from the sheet with the TM1ELLIST? Sounds like it but just confirming.

I do remember having an issue where objects did not refresh as it did not see the sheet as dirty.
I *think* I may have dirtied the sheet through VBA.

Try dirty the sheet manually then run the Macro with the RefreshSheet and see if you get a better result.
The code is currently using Reporting.RefreshSheet(). It converts the results of the TM1ELLIST to static values. When the "filter" is changed, the code writes the new MDX into a cell, clears the old list, throws down TM1ELLIST, calculates, executes Reporting.Refresh(), then value-pastes the results once more.

By that effort the target worksheet would be considered dirty the moment the MDX range is updated.

Every few attempts at executing this code, the Refresh() also refreshes the last report that was active. This is absolutely NOT what I want. >__<

I'd love to skip all this unreliable dancing and make a direct pull from the Rest API but I'm still trying to wrap my head around that mess.
War teaches us geography, getting old teaches us biology.
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

I bounced this issue against someone with far more experience than myself, I will share this sage advice…
The Guru LeRoux wrote:Do not use TM1ELLIST, use the PAfE List object instead.
This hamstrings the idea of using custom MDX to populate that list, however, it also eliminates the need for me to develop a custom VBA interface to build out that MDX. I think that is a solid win.

Maybe in the near-future IBM will expose the MDX portion of the List object? I can hope?

Now to post my next inane question on the forum. Muahahaha.
War teaches us geography, getting old teaches us biology.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: PAfE API: RefreshSheet

Post by ascheevel »

20 Ton Squirrel wrote: Tue Apr 12, 2022 7:39 pm I'm curious if there is something like:

Code: Select all

Reporting.QuickReports.Get(qrIDNumber).Refresh
There is, but it's not well documented in the PAX API documentation. The below code will refresh whichever current report the cell "B2" finds itself in. You could of course use named ranges, ActiveCell, or specify other ranges

Code: Select all

Reporting.GetCurrentReport(Range("B2")).Refresh

20 Ton Squirrel wrote: Tue Apr 12, 2022 7:39 pm The worksheet I'm trying to update doesn't have any report objects on it. It just has TM1ELLIST. There's no specific report to refresh, it just needs to update the results of that function for that particular range. The steps in the code are like this:
  1. Update the MDX
  2. Turn off calculation and screen updating
  3. Clear out range where values existed before
  4. Throw down TM1ELLIST at top of range with updated MDX
  5. Calculate that worksheet ONLY, also includes calls to Cognos API for Reporting.RefreshSheet
  6. Translate results of TM1ELLIST to values
  7. Turn on calc/screen updating
If I don't include the API call for RefreshSheet, the TM1ELLIST just returns "RECALC_0_0" which is a placeholder the function uses while the API is fetching data.
Addressing your last point first, the reason you're seeing "Recalc..." when your macro runs without the RefreshSheet is because I think the worksheets().recalculate vba is not the same as hitting F9 on your keyboard. When you F9 or shift-F9, the PAfE add-in is automatically refreshing the TM1 function TM1Ellist at the same time. When you try to do a simple recalc in VBA, the PAfE add-in automatic piggyback on the recalc is blocked due to the execution of the VBA which is why you need the RefreshSheet. I'm not an excel calculation order expert or have any knowledge of how the PAfE works under the hood, so careful quoting my interpretation.

I don't fully grasp your intended use of the array returned by TM1ELLIST or if/how it relates to your quick reports on another sheet, but in looking over your list, what you've laid out should work. I set up a sample file that had 3 quick reports on 1 worksheet "Sheet2". This worksheet also has a dropdown list with Actual and Budget as available options. I then have a formula reference the selection to create an MDX statement that will be used in a TM1ELLIST formula. On another sheet "Sheet1", I have space for a TM1ELLIST formula that is updated by button click on the sheet with the quick reports. I can toggle/rerun any number of times and the quick reports NEVER refresh unexpectedly. I even set the context filter values for a couple quick reports to reference the Actual/Budget dropdown selection and they will not refresh from the macro. Based on this, I'm inclined to believe that there's something wrong with your code that needs further debugging. I'll paste my quick and dirty macro at bottom for reference. Do you by change have "type-in-refresh" enabled for quick reports in your PAfE options?


20 Ton Squirrel wrote: Mon Apr 11, 2022 8:08 pm I need to selectively update data. Is this possible?
Yes, by using RefreshSelection instead of RefreshSheet, see both options in macro at bottom


20 Ton Squirrel wrote: Tue May 10, 2022 3:27 pm I bounced this issue against someone with far more experience than myself, I will share this sage advice…
The Guru LeRoux wrote:Do not use TM1ELLIST, use the PAfE List object instead.
Did they give a reason or did they just say not to use it? Advice or direction proffered without supporting evidence is to be met with skepticism in my opinion. If they gave you good reason not to use TM1ELLIST and you didn't share it with the rest of us, shame on you. I've used TM1ELLIST for a few different solutions and found it to work just fine for my needs. I'm genuinely curious how a PAfE list object would be the preferred solution in your specific use case considering the issues you're having making that fit your needs.



my macro that worked without issue:

Code: Select all

Sub tm1ellist_test()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim vMDX As String

' recalc for mdx formula update based on selection
Application.Calculate

' mdx formula is on same worksheet as 3 quick reports
vMDX = Range("C2").Value

' activate sheet that has the TM1ELLIST range
Worksheets("Sheet1").Activate

' clear existing values in range
Range("K2").Select
Do Until ActiveCell = ""
    ActiveCell.Delete
    ActiveCell.Offset(1, 0).Select
Loop
Application.CutCopyMode = False


' insert TM1ELLIST formula
Range("k2").Formula2R1C1 = _
        "=TM1ELLIST(""<server>:<dimension>"",,,,,""" + vMDX + """)"

' refresh sheet with TM1ELLIST formula
'Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet

' OR select cell formula pasted into and refresh just that selection (excel spill will still return full array)
Range("k2").Select
Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSelection


' paste values and clear clipboard
Range("K2").Select
Do Until ActiveCell.Offset(1, 0) = ""
    ActiveCell.Offset(1, 0).Select
Loop
vStop = ActiveCell.Address
Range("K2", vStop).Copy
Sheets("Sheet1").Range("K2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
    


' return to worksheet with quick reports
Worksheets("Sheet2").Activate
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub
Last edited by ascheevel on Thu May 12, 2022 2:34 am, edited 1 time in total.
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

Thanks for taking a stab at this, ascheevel.

The rational to use a List instead of TM1ELLIST was…
  • My advisor is a veteran of TM1 but not of VBA, so he couldn't directly debug. I am the only VBA expert in my company, it is a tragically underrated skill.
  • My steps seemed rational but the behavior was inexplicable, I was wasting time trying to figure it out.
  • I'm reinventing a widget that IBM already provides. Keep it simple so others can pick it up easily in case I get hit by a bus.
  • TM1ELLIST has a bug that will not return a specified alias. My principal names are all GUID with aliases. I used Bedrock dim clone and alias swap to automate a "legible" copy of the dimensions being targeted. Since the List object uses the standard set editor there aren't the same limitations.
Despite the sage advice, I tweaked my VBA to use RefreshSelection instead of RefreshSheet…

IT FREAKIN WORKS NOW!!!

I'm gasping. THIS IS SO COOL.

«runs victory laps around the office»

THANK YOU ASCHEEVEL. I owe you at least a beer or something.

So I might just press on with this bit of clever code instead of the List object. Muahahaha. 😈

I made this into a class object, I might post it up here once I get it cleaned up sufficiently. For now I'm going to go spaz out some more. YAYYYY 😜
War teaches us geography, getting old teaches us biology.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAfE API: RefreshSheet

Post by Wim Gielis »

Hello ascheevel,

I have a few questions on the VBA code if I may.

I see a loop to delete cells. Isn't it just Range("K2").ClearContents ? I guess that K2 has the formula (TM1ELLIST) that spills down to a number of cells below K2.

In any case, if you want to retrieve the range to which K2 is spilling, there is a property for that:

Code: Select all

Debug.Print Range("K2").SpillingToRange.Address(0, 0)
Hence, turning that range into hard values, I would suggest:

Code: Select all

Range("K2").SpillingToRange.Value = Range("K2").SpillingToRange.Value
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAfE API: RefreshSheet

Post by Wim Gielis »

In the past I have used such code (with a recent Excel version):

Code: Select all

Sub create_ellist()

    Dim vMDX As String
    
    vMDX = "TM1FilterByLevel( TM1SubsetAll( [Year] ), 0 )"

    'creating a TM1ELLIST function as a dynamic array
    Range("A1").Formula2 = "=TM1ELLIST(""servername:Year"",,,,,""" & vMDX & """)"
    Range("B1").Formula2 = "=""Y"" & A1#"

End Sub
Notice the blue border around the dynamic array formulas.

Sample output:
001.PNG
001.PNG (117.51 KiB) Viewed 3018 times
Last edited by Wim Gielis on Thu May 12, 2022 8:23 pm, edited 1 time in total.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: PAfE API: RefreshSheet

Post by ascheevel »

Wim Gielis wrote: Wed May 11, 2022 10:01 pm I have a few questions on the VBA code if I may.
me [well aware of Wim's excel prowess]: this is gonna hurt


Wim Gielis wrote: Wed May 11, 2022 10:01 pm I see a loop to delete cells. Isn't it just Range("K2").ClearContents ? I guess that K2 has the formula (TM1ELLIST) that spills down to a number of cells below K2.
The first loop is deleting any previously hard coded values. In 20 ton's logic, they're converting their TM1ELLIST results to values. I didn't test, but I figured if the TM1ELLIST results had been converted to values, simply clearing the contents of "K2" would leave the remaining list items intact. I fully appreciate if the formulas was intact then I naturally would only need to clear "K2". If the formula was intact, I suspect 20 ton could've skipped all this vba monkey business and simply hit F9 to refresh a properly written TM1ELLIST formula. For sure there's better ways to select a range of unknown length and delete, but this was intended to be a quick and dirty macro and it'd been a while since I have done much in VBA so fell back on something simple that I could recall. I debated only posting the PAfE relevant macro functions, but decided to post the whole thing in case 20 ton didn't believe me when I said I see it working without issue.


Wim Gielis wrote: Wed May 11, 2022 10:01 pm

Code: Select all

Debug.Print Range("K2").SpillingToRange.Address(0, 0)

Code: Select all

Range("K2").SpillingToRange.Value = Range("K2").SpillingToRange.Value
This is fantastic, thank you for sharing.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAfE API: RefreshSheet

Post by Wim Gielis »

You are right, in VBA it's not easy to bring up a cell and the cells below.

I would consider:

Code: Select all

Sub test()

    Dim r As Range

    With ActiveCell
    
        If IsEmpty(.Offset(1).Value) Then
        
            Set r = .Cells(1)
            
        Else
        
            Set r = Range(.Cells(1), .End(xlDown))
        
        End If
    
    End With
    
    MsgBox r.Address

End Sub
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

I hadn't known about the SpillRange property, that's pretty awesome. Thanks, Wim!

VBA is one of my most beloved and detested skills, I can't help but offer another tidbit for the situation. When dealing with ranges where I'm not sure about the size, I occasionally use COUNTA across what I know to be the max extent.

Code: Select all

… «brilliant code above here» …

  'We need the target range active/selected, so make that happen (even if temporary)
    If (ActiveSheet.Name <> vwks.Name) Then
        vwks.Activate
    End If
    
    Set rng = vwks.Range(strAdrElement)
    rng.Select
            
  'Clear original values out, we don't care about anything under it
    rng.Resize(RowSize:=50000).ClearContents
     
  'Update the range with the TM1ELLIST, calc, and force Cognos to freshen up
    rng.Formula2 = "=TM1ELLIST(Core.Server&"":""&" & uDIMFILTER_ADR_NAME_REMOTE_ALIAS & ",,,,," & strAdrMDX & ")"
    vwks.Calculate
    cog.Reporting.RefreshSelection  'OMFG THIS IS AMAZING THANKS ASCHEEVEL
    cog.Reporting.Wait
    
  'Determine new count and update named ranges
    lngRowCount = WorksheetFunction.CountA(rng.Resize(RowSize:=50000))
    
  'Value-paste formula range
    Set rng = rng.Resize(RowSize:=lngRowCount)
    rng.Formula2 = rng.Value2
    
 … «brilliant code continues» …
This assumes values within that range are contiguous, mind you. Determining the range extent and working with that directly is more efficient than looping over range values.

Also, if you're needing to loop through values, it is MUCH faster to dump the range into an array and loop THAT instead of hitting each Range.Value. It is also faster to scoop up that array, modify that data in the procedure(s), then drop it back down on the sheet when you're done… that way VBA is only writing to the worksheet ONCE.

Code: Select all

  Dim varDat as Variant
  Dim lngRow as Long, lngRowCount as long
  Dim rng as Range
  
    Set rng = ActiveSheet.Range("A1:D432921")
    lngRowCount = rng.Rows.Count
    varDat = rng.Value2
    For lngRow = 1 to lngRowCount
        If (varDat(lngRow, 3) = "Muffin") Then
            ExecuteBrilliantProcedureOnRow varDat, lngRow
        End If
    Next lngRow
 
    rng.Value2 = varDat
    
Apologies for the pedantry, hope it helps. 🤓
War teaches us geography, getting old teaches us biology.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAfE API: RefreshSheet

Post by gtonkin »

Using something like

Code: Select all

lngRowCount=ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Row
may be useful instead of guessing an end row.
On my phone so not tested this…
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

@GTonkin: If I recall correctly, xlCellTypeLastCell returns the very last cell in the worksheet's used range, even if you're calling that on a defined range it will return OUTSIDE of it. This would be useful if your data is isolated on the worksheet but might return unexpected results if you have data scattered all over the worksheet.
War teaches us geography, getting old teaches us biology.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAfE API: RefreshSheet

Post by gtonkin »

You are correct. I use it if I need to find the last row that may be in use the work upwards, instead of settling on a constant e.g. 5000 rows. Your DCountA combined with this is what I was thinking to give you your last row.
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API: RefreshSheet

Post by 20 Ton Squirrel »

gtonkin wrote: Thu May 12, 2022 7:39 pm You are correct. I use it if I need to find the last row that may be in use the work upwards, instead of settling on a constant e.g. 5000 rows. Your DCountA combined with this is what I was thinking to give you your last row.
That's pretty clever for non-contiguous data, actually. Thanks, George!
War teaches us geography, getting old teaches us biology.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: PAfE API: RefreshSheet

Post by Wim Gielis »

Hello,

Consider:

Code: Select all

Sub test()

    Dim rRealUsedRange As Range
    Dim lLastRow As Long
    
    With ActiveSheet.UsedRange
    
        Set rRealUsedRange = .Cells(1).Resize(.Rows.Count, .Columns.Count)
        
        lLastRow = .Cells(1).Row + rRealUsedRange.Rows.Count - 1
        
    End With
    
    MsgBox rRealUsedRange.Address(0, 0)
    MsgBox lLastRow

End Sub
Note that the UsedRange of a sheet does not necessarily start in cell A1.
If you only enter stuff in V5:Z10 then that range will the used range.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply