Deleting entire columns from a value found

Question

So i want a value from a text box, that is in a form, to be looked for, then i want the value's column and the columns from the left and the right to get deleted. But for some reason the Rango.Column is not getting the columns correctly. The code successfully finds the value.

Private Sub CommandButton1_Click()
Dim Rango As Range

Dim Prueba_N As String

    Prueba_N = Partida.Txt.Value
    If Prueba_N <> 0 Then
        With Sheets("Prueba").Rows("6:6")
            Set Rango = .Find(What:=Partida_N, After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

        End With

            RangoC_1 = Rango.Column - 1
            RangoC_2 = Rango.Column + 1
            Sheets("Prueba").Range(Cells(1, RangoC_1), Cells(1, RangoC_2)).EntireColumn.Delete
            Finalizar = MsgBox("Se ha eliminado la partida exitosamente")
            Partida.Hide
            Sheets("Materiales").Activate

    Else
        Partida_Err = MsgBox("Agregar Partida", vbCritical)
    End If

End Sub

I also dont know why sometimes if i start the partida's form from the button it returns me the 1004 error on the Sheets("Prueba").Range... line but if i start the form directly it doesnt give me the error.

Possible duplicate of [Why does Range work, but not Cells?](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) — BigBen, Jul 10 '19 at 18:55
Also - test `If Not Rango Is Nothing` - you're assuming that the `Find` is successful. — BigBen, Jul 10 '19 at 18:56
Not duplicate because im testing it in a sheet that only has the value that im looking for. And already tried the If Not Rango is nothing And since here the find will always be succesful i didnt need to add that. Also for some reason its adding numbers in certain cells idk why. — Armas.Chuy, Jul 10 '19 at 18:59
Qualify the worksheet that the `Cells` are on. That's why it's a dupe. You can use a `With...End With` block. — BigBen, Jul 10 '19 at 19:00
Everything Starting on Rango is in Partida's sheet u mean i should make the with that already has bigger? — Armas.Chuy, Jul 10 '19 at 19:05
No. You have `Cells(1, RangoC_1), Cells(1, RangoC_2)`. There's no `Worksheet` specified before those `Cells`, so there's an implicit `ActiveSheet`. The `ActiveSheet` may not be `Sheets("Prueba")`. Also - what if the match is found in Column A? That will throw an error as well. I guess that's the point of the posted answer. — BigBen, Jul 10 '19 at 19:06
You're still missing a period `.` in front of those `Cells` calls. That's the same error. — BigBen, Jul 10 '19 at 19:11
It will never be in column A simply because the format on the original code will be always at least on column B. Right now im doing it on column D on the test sheet. Btw now the 1004 error is not appearing but its still not working :( — Armas.Chuy, Jul 10 '19 at 19:15
"And since here the find will always be succesful i didnt need to add that" - fine, but don't ask why you're getting error 91 later. — Mathieu Guindon, Jul 10 '19 at 19:18
"It will never be in column A simply because..." until someone changes the layout because they don't know that the layout of columns is critical to making the code work. @BigBen's suggestions are simple things that will make your code much more robust so you (or a coworker) don't have to come back here in a year and ask, "this has been working for a year and now all of a sudden it doesn't. What happened?" — FreeMan, Jul 10 '19 at 19:18
I understan that @FreeMan but the whole point of the code is deleting a whole format, which is 3 columns lenght 1 on the left and 1 on the right from the value that im looking for. — Armas.Chuy, Jul 10 '19 at 19:27
@Armas.Chuy as a general rule, it's not appropriate to update your OP _code_ with fixes along the way. That makes it difficult for someone coming along in the future to identify how the selected answer (or comments or unselected answer(s)) addressed/fixed the original problem - there's significant future value in that as well. It's quite OK to leave broken code posted - that's what this site is all about. It's perfectly OK to post additional code (particularly if it's requested) to help your helpers get "the big picture", or to add additional explanatory text to clarify the problem. — FreeMan, Jul 10 '19 at 19:48

learnAsWeGo · Accepted Answer · 2019-07-10T19:26:58.553

1

You put the txt.value into a var named Prueba_N but then search for Partida_N

**Prueba_N** = Partida.Txt.Value
If Prueba_N <> 0 Then
    With Sheets("Prueba").Rows("6:6")
        Set Rango = .Find(What:=**Partida_N**, After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    End With

Should be

**Partida_N**, = Partida.Txt.Value
If Prueba_N <> 0 Then
    With Sheets("Prueba").Rows("6:6")
        Set Rango = .Find(What:=**Partida_N**, After:=.Cells(.Cells.Count), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    End With

Also

Rows("6,6")

Searches Row 66

You want

Rows("6")

edited Jul 10 '19 at 19:26

answered Jul 10 '19 at 18:59

learnAsWeGo

2,252
2
13
19

No, since im trying it out on a test sheet, also in the actual code it won't ever be 0. – Armas.Chuy Jul 10 '19 at 19:01
Right now its on column D, as i said on the answer above, in the original code will be at least on B. – Armas.Chuy Jul 10 '19 at 19:16
Yeah im look for row 6 – Armas.Chuy Jul 10 '19 at 19:24
You are searching row 66, see update. Also you are searching for a variable that is never set – learnAsWeGo Jul 10 '19 at 19:31
Indeed im dumb :( Thank you – Armas.Chuy Jul 10 '19 at 19:34
3

@Armas.Chuy add `Option Explicit` at the top of *every module* in the project, then try to compile. If it doesn't compile, you are using variables that aren't declared anywhere... which I suspect `Partida_N` is. – Mathieu Guindon Jul 10 '19 at 19:36

Deleting entire columns from a value found

1 Answers1