Simple Question: How to locate the last populated cell in a column of a PlanMaker worksheet?

[ozboomer]

Hi, all... another newbie here (in BasicMaker, anyway)... and I haven't done any VBA-style things in a spreadsheet since the 1990s or something(!)

So, a bit of a simplistic question:

I have a worksheet that has a title row and then some populated rows. I simply want to find the last non-empty row in a specific column. Consider:

Code: Select all

n = pm.ActiveSheet.Columns(3).Rows.Count

...is no good as it just returns the dimension of the worksheet (1,048,576 rows).

Short of iterating through each row, examining each cell, is there any function/builtin that will return the last populated cell within a column? ...or even the first non-populated cell? I've tried hitting the manual... but it's sort of like working through seaweed... and I haven't been able to find a relevant example there.

I'm simply trying to build a script to apply a display attribute to an entire row based on a value in the row in a certain column. The 'sensible' thing to do would be to use Conditional Formatting... but I've literally spent days trying to get that to work when rows are inserted or appended to the existing rows of data and everything goes haywire.. hence this foray into BasicMaker. Ultimately, it'll be quicker to get the job done this way than to spend more time trying to get 'me of little brain' to come to grips with Conditional Formatting... and it'll be useful to know about anyway.

Although I've used VBA-like tools (SaxBasic, WinWrapBasic, etc) in a number of applications, I'm still trying to learn the extent of the Object Model here... and how it works.

I'm using PlanMaker 2021 (rev S1068.1105) and BasicMaker Professional 2021 (rev S1068.1105) , both 32-bit, under Windows 8.1 32-bit.

Thanks a heap.

[SuperTech]

Thank you for your post, but currently this is not available in BasicMaker. I have forwarded this improvement suggestion.

[robert006]

I second this request - it would make BasicMaker more useful for programming Planmaker.

[Recycler]

I'm simply trying to build a script to apply a display attribute to an entire row based on a value in the row in a certain column.

Hello ozboomer! I wonder if you could help me please? I seem to be trying to do exactly what you were asking about in this post (apply a display attribute to a row based on a value in the row in a certain column).....

However I'm struggling to get off the starting block, and my attempts to work through the object model have so far been messy, unsuccessful and (probably) incomplete:

Code: Select all

' Open the PlanMaker document

Dim doc As Object
Set doc = GetObject("PlanMaker.Application")
doc.Visible = True
doc.Documents.Open("C:\Data\DHC Faculties\Play version for experiments.pmdx")

Dim wb As Object
Set wb = doc.Worksheets

' Loop through rows in the desired sheet
Dim sheet As Object
Set sheet = wb.Sheet("Data Dump")

For row = 1 To sheet.UsedRange.Rows.Count
    Dim cellValue As String
    cellValue = sheet.Cells(row, 18).Value
    Dim lastDigit As Integer
    lastDigit = CInt(Right(cellValue, 1))
    
    ' Set color based on lastDigit
    Select Case lastDigit
        Case 1
            sheet.Rows(row).Interior.Color = RGB(255, 200, 200) ' light red
        Case 2
            sheet.Rows(row).Interior.Color = RGB(200, 255, 200) ' light green
        ' ...and so on for digits 3-9
    End Select
Next

I'd be grateful for any guidance you can provide. Thanks.