I know theres simple fix for this

Xcel90pro

New member
Joined
Jun 9, 2014
Messages
6
Reaction score
0
Points
0
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:
[COLOR=#333333]Set Found = Columns("A").Find(What:="End", After:=[A1], LookIn:=xlFormulas, _[/COLOR]
[COLOR=#333333]LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _[/COLOR]
[COLOR=#333333]MatchCase:=False, SearchFormat:=False)[/COLOR]
[COLOR=#333333]If Found Is Nothing Then[/COLOR]
[COLOR=#333333]sMessage = "No Match Found for ""End""" & vbCr[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]sMessage = """End"" found in cell " & Found.Address & " Row: " & Found.Row & vbCr[/COLOR]
[COLOR=#333333]s = Found.Row[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]MsgBox sMessage[/COLOR]
[COLOR=#333333]MsgBox s[/COLOR]




[COLOR=#333333]'////////[/COLOR]



Range(Cells(33, "A"), Cells([B]s[/B], "A")).Clear
Range(Cells(33, "B"), Cells([B]s[/B], "B")).Clear
Range(Cells(33, "C"), Cells([B]s[/B], "C")).Clear
Range(Cells(33, "D"), Cells([B]s[/B], "D")).Clear
Range(Cells(33, "E"), Cells([B]s[/B], "E")).Clear
Range(Cells(33, "F"), Cells([B]s[/B], "F")).Clear
Range(Cells(33, "G"), Cells([B]s[/B], "G")).Clear
Range(Cells(33, "H"), Cells([B]s[/B], "H")).Clear
Range(Cells(33, "N"), Cells([B]s[/B], "N")).Clear
Range(Cells(33, "O"), Cells([B]s[/B], "N")).Clear
Range(Cells(33, "P"), Cells([B]s[/B], "P")).Clear
Range(Cells(33, "R"), Cells([B]s[/B], "R")).Clear
Range(Cells(33, "T"), Cells([B]s[/B], "T")).Clear
Range(Cells(33, "U"), Cells([B]s[/B], "U")).Clear
Range(Cells(33, "V"), Cells([B]s[/B], "V")).Clear
Range(Cells(33, "W"), Cells([B]s[/B], "W")).Clear
Range(Cells(33, "Z"), Cells([B]s[/B], "Z")).Clear
Range(Cells(33, "AA"), Cells([B]s[/B], "AA")).Clear
 
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
What does MsgBox s give you?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
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
 
Top