Converting javascript to VBA macro [duplicate]

Question

I'm having trouble finding a converter from a javascript to VBA macro. Can anyone tell me how to accomplish this function as a macro? It hides any row from 4-94 that has a null value in its column A, then continues doing this through the first 12 tabs/worksheets. The macro below works if I run it for each worksheet individually, but not after I have to lock the affected section.

function StepThroughTabs(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();//Get the spreadsheet name
  var startrow = 4;//establishing the initial header row 
  var maxrows = 94;//Establishing the final row
  for (var x = 0; x < 12; x++) {  //Step through the first 12 tabs
     var y = ss.getSheets()[x]; //Which tab are you on?
     y.showRows(1,maxrows) //grab an array of rows
     var vals = y.getRange(1,1,maxrows,1).getValues(); // The second number is the column I want to evaluate, in this case it is column A as a 1
     for (var i = startrow; i < maxrows; i++) if (vals[i] == "") y.hideRows(i+1); // Hide the row based on the value found

  }
}

I've tried the following code that requires manual selection of the sheets that I want to run but it keeps getting hung up at Set rng = Range("A5:A93") when I save after the workbook is locked. Unfortunately, locking the workbook cannot be avoided.

Sub HideLoop()
 Application.ScreenUpdating = False
 'Create variable to hold worksheets
 Dim ws As Worksheet

'Loop through each selected worksheet
For Each ws In ActiveWindow.SelectedSheets

'Perform action.  E.g. hide selected worksheets
Dim c As Range, rng As Range
Set rng = Range("A5:A93")
rng.EntireRow.Hidden = False
For Each c In rng
    If c.Value = vbNullString Then c.EntireRow.Hidden = True
Next c

   Next ws
   Application.ScreenUpdating = True

   End Sub

Thank you for any assistance!

Always qualify your ranges with a sheet refernce: Set rng = Range("A5:A93") becomes Set rng = ws.Range("A5:A93") — MacroMarc
– MacroMarc, Commented Jun 3, 2021 at 21:45

Tim Williams · Accepted Answer · 2021-06-03 21:50:56Z

0

Something like this (untested)

Sub StepThroughTabs()
    Const NUM_ROWS As Long = 94
    Const START_ROW As Long = 4
    Dim shtNum As Long, wb As Workbook, arr, rng As Range, i As Long
    
    Set wb = ThisWorkbook 'for example
    Application.ScreenUpdating = False
    For shtNum = 1 To 12
        Set rng = wb.Worksheets(shtNum).Cells(1, 1).Resize(NUM_ROWS)
        arr = rng.Value
        rng.EntireRow.Hidden = False
        
        For i = START_ROW To NUM_ROWS
            If Len(arr(i, 1)) = 0 Then rng.Cells(i).EntireRow.Hidden = True
        Next i
    Next shtNum
End Sub

answered Jun 3, 2021 at 21:50

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

7ower Over a year ago

Ah! Const and shtNum! Thank you so much, Tim. This executed perfectly without any modification needed. I am embarrassed to say how many other routes i attempted on my own. Still learning though. Thank you to everyone who helped! Cheers!

Collectives™ on Stack Overflow

Converting javascript to VBA macro [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related