Results 1 to 3 of 3

Thread: I know theres simple fix for this

  1. #1

    I know theres simple fix for this



    Register for a FREE account, and/
    or Log in to avoid these ads!

    So The first part of the code is just searching the columns to see how long they are and the second part of the code is deleting the columns of Size s that we just found from the search!! Now every other time ive done this type of thing plugging in s as variable would work and it would just plug in the value for the row but for some reason in this case its not doing it. Is there a way I can print out s and put it in all thoughs row values.





    Code:
    Set Found = Columns("A").Find(What:="End", After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Found Is Nothing Then
    sMessage = "No Match Found for ""End""" & vbCr
    Else
    sMessage = """End"" found in cell " & Found.Address & " Row: " & Found.Row & vbCr
    s = Found.Row
    End If
    MsgBox sMessage
    MsgBox s
    
    
    
    
    '////////
    
    
    
    Range(Cells(33, "A"), Cells(s, "A")).Clear
    Range(Cells(33, "B"), Cells(s, "B")).Clear
    Range(Cells(33, "C"), Cells(s, "C")).Clear
    Range(Cells(33, "D"), Cells(s, "D")).Clear
    Range(Cells(33, "E"), Cells(s, "E")).Clear
    Range(Cells(33, "F"), Cells(s, "F")).Clear
    Range(Cells(33, "G"), Cells(s, "G")).Clear
    Range(Cells(33, "H"), Cells(s, "H")).Clear
    Range(Cells(33, "N"), Cells(s, "N")).Clear
    Range(Cells(33, "O"), Cells(s, "N")).Clear
    Range(Cells(33, "P"), Cells(s, "P")).Clear
    Range(Cells(33, "R"), Cells(s, "R")).Clear
    Range(Cells(33, "T"), Cells(s, "T")).Clear
    Range(Cells(33, "U"), Cells(s, "U")).Clear
    Range(Cells(33, "V"), Cells(s, "V")).Clear
    Range(Cells(33, "W"), Cells(s, "W")).Clear
    Range(Cells(33, "Z"), Cells(s, "Z")).Clear
    Range(Cells(33, "AA"), Cells(s, "AA")).Clear
    Last edited by NBVC; 2014-06-12 at 06:13 PM.

  2. #2
    What does MsgBox s give you?

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    as xld says, what is s when you run this?
    Your code works here if s is a number.
    You could save yourself some typing by cutting all those .clear lines to one:
    Range("A" & s & ":H33,N" & s & ":P33,R" & s & ":R33,T" & s & ":W33,Z" & s & ":AA33").Clear

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •