Posts by VBA Noob

Re: Hide Or Filter Rows Based On Cell Value

Dave,

Cross posted here

http://www.mrexcel.com/forum/showthread.php?t=360383

VBA Noob

Re: Hide Rows Based On Multiple Criteria

Does this help

Sub HideRows()


    Rows("2:11").EntireRow.Hidden = _
    IIf(Range("Z7").Value = Range("AJ7").Value, True, False)
End Sub

VBA Noob

Re: Sum Conditional Formatted Cells By Color

That would be a good start

VBA Noob

Re: Populate Column With Later Dates

leopardhawk,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://174.133.212.43/showthread.php?t=651922

VBA Noob

Re: Clear Cell When Another Is False

I assume your code is doing the selecting. You don't need to select to delete a cell

e.g This clears the cell

Sheets("Sheet1"). Range("C7").ClearContents

VBA Noob

Re: Clear Cell When Another Is False

Try this in sheet 2.

Private Sub Worksheet_Activate()
    If Sheets("Sheet1").Range("C7") = "False" Then
            Call DeleteK17
    End If
End Sub

VBA Noob

Re: Bordering A Changing Group Of Cells

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

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

Cells.Select ' Not Required
Cells.EntireColumn.AutoFit 
ActiveWindow.SmallScroll ToRight:=7 'Not Required
Columns("K:N").Select  ' Change this line and
Selection.ClearContents ' this one to Columns("K:N").ClearContents
ActiveWindow.SmallScroll ToRight:=-7 'Not Required
Range("A2").Select  'Not Required
Rows("2:2").RowHeight = 49.5 
lastrow1 = Range("A65536").End(xlUp).Select 
Range("A5:J" & lastrow1).BorderAround.Weight = xlThin 
lastrow2 = Cells(Cells.Rows.Count, "C").End(xlUp).Row 
For I = lastrow2 To 2 Step -1 
    If Cells(I, "C").Value <> Cells(I - 1, "C").Value Then Rows(I).Insert 
Next I 
 
With ActiveSheet.PageSetup 
    .Orientation = xlLandscape 
    .PaperSize = xlPaperA4 
    .FirstPageNumber = xlAutomatic 
    .FitToPagesWide = 1 
    .FitToPagesTall = 1 
    .PrintErrors = xlPrintErrorsDisplayed 
End With 
 
End Sub

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

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: Scroll Lock

jhenderson,

Thanks for sharing

VBA Noob

Re: Delete Row If Date Is Greater Than Or Equal To 6/6/2008

dcnorman07,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://www.excelforum.com/showthread.php?t=647471

PM Dave Hawley if you agree to the forum rules

VBA Noob

Re: Saving Sheet As Tab Delimited Text

GustavBA,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://www.excelforum.com/showthread.php?t=647315

PM Dave Hawley if you agree to the rules

Re: Lock Scroll Area

Try

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: Drop Down Lists Populating Formulas?

TampaPromoGirl,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://www.excelforum.com/showthread.php?t=647023

PM dave Hawley if you agree to the rules

VBA Noob

Re: Stop Code At Certain Time

One way

Sub LoopUsingTime()
     
   Dim x As Long
   x = 1
    
    For lCount = 1 To 60
        Sheet1.Cells(x, 1) = "the time is now " & Now()
        x = x + 1
        Application.Wait (Now + TimeValue("0:00:01"))
    Next lCount
     
    MsgBox ("loop finished")
     
End Sub

VBA Noob

Re: Disable Autocomplete For A Specific Cell

Works for me.

Only can go in the sheet hence my instructions

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

Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = LastRow To 1 Step -1
    If Weekday(Cells(i, "A"), 2) > 5 Then
        ' delete cell up
        Cells(i, "A").Delete Shift:=xlUp
        ' or to delete entire row
        'Cells(i, "A").EntireRow.Delete
    End If
Next i

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
    If Target.Address(0, 0) = "A11" Then
        .EnableAutoComplete = False
    Else
        .EnableAutoComplete = True
    End If
End With


End Sub

VBA Noob

Re: Autofit All Columns In Many Worksheets To Cell Content

Without selecting

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: How do I count dates in a 12 month rolling period?

butlej6,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://www.excelforum.com/showthread.php?t=646256

Please PM Dave Hawley if you agree to the forum rules

VBA Noob

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

You could also remove some of the conditions by using LCase

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Long
    Dim c As Range
        
    If Not Intersect(Target, Range("B2:Q53")) Is Nothing Then
        For Each c In Range("B2:Q53")
            Select Case LCase(c.Value)
            Case "ibbch":     icolor = 36 'light yellow
            Case "obbch":     icolor = 34 'light turqoise
            Case "obbrdg":    icolor = 35 'light green
            Case "lnch":      icolor = 53 'brown
            Case "brk":       icolor = 15 'gray-25%
            Case "av":        icolor = 10 'green
            Case "as", "med": icolor = 3 'red
            Case Else
                 icolor = 0
            End Select
            c.Interior.ColorIndex = icolor
        Next c
    End If
End Sub

VBA Noob