how to limit rows and columns with VBA?

[GingerBeardo]

I have several sheets that have hidden rows and columns but on occasion I need to unhide them. This usually results in a freeze/crash of the sheet due to the volume of columns being unhidden. How can I limit the number of both rows and columns to something like 15 and 50, respectively?

 

[cmowla]

First of all, I don't know what your sheet looks like, but you should perhaps read this list of 10 Tricks to Fix Slow Excel Spreadsheets. Even though I knew what I was doing in a lot of other areas up until that point, I remember that Point #1 Avoid Volatile Formulas really helped one of my sheets. (I had TODAY() in thousands of cells. But after I read that, I simply changed the Workbook calculate it upon opening the Excel file and store today's date as a string in a cell. Then I referenced that cell. This made my sheet so much faster!)

But anyway, if you are still keen on doing this, do you want to have VBA unhide like 15 columns at a time instead of all and take a short break in between? Or do you only really need to hide/unhide a specific (select) columns, but are hiding/unhiding a contiguous range of columns for simplicity?

And you can modify your VBA sub as so to prevent the freeze (if you haven't done this already)

Application.ScreenUpdating = False
Application.EnableEvents = False

[Code to hide/unhide]

Application.ScreenUpdating = True
Application.EnableEvents = True

 

[GingerBeardo]

Currently, the sheets are completely blank, I'm reworking them, they are live sheets that will need to be constantly updated. This is why I need to hide and unhide so often. I'm not well versed in VBA, only dabbled, is the code you provided to simply turn off hide and unhide? If so that wouldn't work. thought, unhiding 15 at a time would be great.

 

[GingerBeardo]

First of all, I don't know what your sheet looks like, but you should perhaps read this list of 10 Tricks to Fix Slow Excel Spreadsheets. Even though I knew what I was doing in a lot of other areas up until that point, I remember that Point #1 Avoid Volatile Formulas really helped one of my sheets. (I had TODAY() in thousands of cells. But after I read that, I simply changed the Workbook calculate it upon opening the Excel file and store today's date as a string in a cell. Then I referenced that cell. This made my sheet so much faster!)

But anyway, if you are still keen on doing this, do you want to have VBA unhide like 15 columns at a time instead of all and take a short break in between? Or do you only really need to hide/unhide a specific (select) columns, but are hiding/unhiding a contiguous range of columns for simplicity?

And you can modify your VBA sub as so to prevent the freeze (if you haven't done this already)

Application.ScreenUpdating = False
Application.EnableEvents = False

[Code to hide/unhide]

Application.ScreenUpdating = True
Application.EnableEvents = True

First of all, I don't know what your sheet looks like, but you should perhaps read this list of 10 Tricks to Fix Slow Excel Spreadsheets. Even though I knew what I was doing in a lot of other areas up until that point, I remember that Point #1 Avoid Volatile Formulas really helped one of my sheets. (I had TODAY() in thousands of cells. But after I read that, I simply changed the Workbook calculate it upon opening the Excel file and store today's date as a string in a cell. Then I referenced that cell. This made my sheet so much faster!)

But anyway, if you are still keen on doing this, do you want to have VBA unhide like 15 columns at a time instead of all and take a short break in between? Or do you only really need to hide/unhide a specific (select) columns, but are hiding/unhiding a contiguous range of columns for simplicity?

And you can modify your VBA sub as so to prevent the freeze (if you haven't done this already)

Application.ScreenUpdating = False
Application.EnableEvents = False

[Code to hide/unhide]

Application.ScreenUpdating = True
Application.EnableEvents = True

Just realized I posted my responce instead of replied.

 

[Michael M]

Probably need to provide info on which columns / rows you want hidden / unhidden and a code could be provided.

 

[cmowla]

I'm not well versed in VBA, only dabbled, is the code you provided to simply turn off hide and unhide? If so that wouldn't work. thought, unhiding 15 at a time would be great.

No, the center code was just pseudocode. The first two lines "turns stuff off" before you run code. The last two lines turns those things back on. They are not needed when hiding/unhiding columns, but if they are on, they can slow down Excel.

If you want to hide or unhide a contiguous range of columns, here is generalized code to do that. (Run the two *** subs.)

(It may seem like a lot of code, but I generally name my variables like sentences so that it's very self-explanatory. And I don't skip steps and break everything into chunks.)

Option Explicit

#If VBA7 Then 'For 64 Bit Systems
   
    'This is for pausing execution for x milliseconds (virtual key event listener).
    'The call is (without quotes):  "Sleep x".
    'https://stackoverflow.com/questions/24274840/vba-sendkeys-c-seems-to-be-copying-to-a-different-clipboard
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

#Else 'For 32 Bit Systems
   
    'This is for pausing execution for x milliseconds (virtual key event listener).
    'The call is (without quotes):  "Sleep x".
    'https://stackoverflow.com/questions/24274840/vba-sendkeys-c-seems-to-be-copying-to-a-different-clipboard
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If


Sub UnHide_Columns_In_Increments_Of_n_Pausing_In_Between() '***
Call Hide_Unhide_Columns_In_Increments_Of_n_Pausing_In_Between(ActiveSheet.Name, "C", "DF", 15, 0.5, "UnHide")
End Sub


Sub Hide_Columns_In_Increments_Of_n_Pausing_In_Between()'***
Call Hide_Unhide_Columns_In_Increments_Of_n_Pausing_In_Between(ActiveSheet.Name, "C", "DF", 15, 0.5, "Hide")
End Sub


Sub Hide_Unhide_Columns_In_Increments_Of_n_Pausing_In_Between( _
sheetName As String, _
firstColumnLetterToHideUnhide As String, _
lastColumnLetterToHideUnhide As String, _
numberOfColumnsToHideOrUnhideAtATime As Variant, _
numberOfSecondsToPauseInBetween As Long, _
hideOrUnhide As String _
)

'Application.ScreenUpdating = False '(Uncomment this once you see how this works.)
Application.EnableEvents = False

'Convert the column letters to numbers.
    Dim firstColumnNumberToHideUnhide As Integer
    firstColumnNumberToHideUnhide = Range(firstColumnLetterToHideUnhide & "1").Column
   
    Dim lastColumnNumberToHideUnhide As Integer
    lastColumnNumberToHideUnhide = Range(lastColumnLetterToHideUnhide & "1").Column

Dim totalNumberOfColumnsToHideUnhide As Integer
totalNumberOfColumnsToHideUnhide = lastColumnNumberToHideUnhide - firstColumnNumberToHideUnhide + 1

Dim increment As Integer
increment = numberOfColumnsToHideOrUnhideAtATime

Dim numberOfEqualIterations As Integer
numberOfEqualIterations = Floor(totalNumberOfColumnsToHideUnhide / increment)

Dim numberOfColumnsToHideUnhide_InTheLastIteration As Integer
numberOfColumnsToHideUnhide_InTheLastIteration = Modulous(totalNumberOfColumnsToHideUnhide, numberOfColumnsToHideOrUnhideAtATime)

Dim i As Integer
i = firstColumnNumberToHideUnhide

Dim currentIteration As Integer
currentIteration = 1

Do While currentIteration <= numberOfEqualIterations
    With Sheets(sheetName)
        If UCase(hideOrUnhide) = "HIDE" Then
            .Range(.Cells(1, i), .Cells(1, i + numberOfColumnsToHideOrUnhideAtATime - 1)).EntireColumn.Hidden = True
            'MsgBox .Range(.Cells(1, i), .Cells(1, i + numberOfColumnsToHideOrUnhideAtATime - 1)).Address
        Else
            .Range(.Cells(1, i), .Cells(1, i + numberOfColumnsToHideOrUnhideAtATime - 1)).EntireColumn.Hidden = False
        End If
    End With
    Sleep numberOfSecondsToPauseInBetween * 1000
    i = i + increment
    currentIteration = currentIteration + 1
Loop


If numberOfColumnsToHideUnhide_InTheLastIteration > 0 Then
    With Sheets(sheetName)
        If UCase(hideOrUnhide) = "HIDE" Then
            .Range(.Cells(1, i), .Cells(1, i + numberOfColumnsToHideUnhide_InTheLastIteration - 1)).EntireColumn.Hidden = True
            'MsgBox .Range(.Cells(1, i), .Cells(1, i + numberOfColumnsToHideOrUnhideAtATime - 1)).Address
        Else
            .Range(.Cells(1, i), .Cells(1, i + numberOfColumnsToHideUnhide_InTheLastIteration - 1)).EntireColumn.Hidden = False
        End If
    End With
End If

'Application.ScreenUpdating = True '(Uncomment this once you see how this works.)
Application.EnableEvents = True

End Sub

Sub Test__Modulous()
MsgBox Modulous(5, 2)
End Sub
Function Modulous(a As Variant, B As Variant)
Modulous = a - B * Floor(a / B)
End Function

Sub Test__Floor()
MsgBox Floor(3.21)
MsgBox Floor(-3.21)
End Sub
Function Floor(number As Variant)
'Code from here: https://www.mrexcel.com/board/threads/floor-function-in-vba-code.218947/#post-1069232
Floor = Int(number) - 1 * (Int(number) > number)
End Function