Posts by VBA Noob

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Bordering A Changing Group Of Cells


    Try avoid selecting. Below suggestions to avoid selecting and amend code for border


    Code
    Dim LastRow1 As Long
    LastRow1 = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A5:J" & LastRow1).BorderAround _
    Weight:=xlThin



    VBA Noob

    Re: Clearing A Cell When Another Cell Value Is False


    Right Click sheet1 > select view code > paste in the below


    It will call DeleteK17 if C7 equals False


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "C7" Then
        If Target.Value = "False" Then
            Call DeleteK17
        End If
    End If
    End Sub


    VBA Noob

    Re: Lock Scroll Area


    Try


    Code
    Worksheets(1).ScrollArea = "A1:L50"


    Change worksheet 1 to your sheet. You can also set the scroll area in the properties window while in VB


    Also see suggestions below


    VBA Noob

    Re: Stop Code At Certain Time


    One way



    VBA Noob

    Re: Macro To Show Only Working Days


    No need for a macro. Place this in a helper column


    =IF(WEEKDAY(A1,2)>5,"Delete","") then filter on the word delete and then delete selected rows


    or with some code




    VBA Noob

    Re: Disable Autocomplete For A Specific Cell


    Maybe an event macro. Right click the sheet tab > select view code > paste in the below



    VBA Noob

    Re: Autofit All Columns In Many Worksheets To Cell Content


    Without selecting


    Code
    Sub AutofitColumns()
        Dim Wsht As Worksheet
        For Each Wsht In Worksheets
            With Wsht.UsedRange
                .EntireColumn.AutoFit
             End With
        Next Wsht
    End Sub


    VBA Noob

    Re: Exceeding Excel's 3 Criteria Limit In Conditional Formatting


    You could also remove some of the conditions by using LCase



    VBA Noob