"Variable not defined" message. Why? and How do I fix it?

sizemorem

Board Regular
Joined
Dec 12, 2008
Messages
103
When I import some txt files into excel, I have to clean up the gargbage. This is the part of my code for each file to filter and delete. It works in every other file, but in this particular file, it give me a message that the variable is not defined. I know that there are several different ways to define variables, and I am confused on which to use when. Can someone lend a hand?

Code:
Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=" "
    With Sheets("OCVCHK").AutoFilter.Range
          On Error Resume Next
          Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
          On Error GoTo 0
     End With
     If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

Thanks bunches as always
marcia
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
868
Check that you don't have Option Explicit on top of that module.

(If you just to make it work, that's easy way, if you want to do it "the right way" try to add "Dim rngToDelete as range" before Selection.Autofilter)
 
Last edited:

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Do you have Dim statements at the beginning of your code? Also did you put Option Explicit before the Sub?
 

sizemorem

Board Regular
Joined
Dec 12, 2008
Messages
103
Ok, Option Explicit is at the beginning of the code. I just didn't post all of it because it is so long and the opening and formatting work fine. This is where it stops.

No I have not got any dim statements. The file that I copied this code from was working just fine and it did not have any dim statements. I am still learning and variables confuse me the most.

I will try Jaymond's suggestion. I actually have about five of these filters in the code. Do I need to include the dim statement before each one, or will once at the start be enough. I know that the code actually turns the autofilter off and back on again each time, but the last when it just turns off.

SO MUCH TO LEARN. SO LITTLE TIME.
but I keep trying
marcia
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

a DIM is only needed one time at the start of your code (after the Sub)
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
also check
In the VBA Editor: TOOLS > OPTIONS > EDITOR tab > select/unselect "Require Variable Declaration"

to uncheck will turn off the declaration requirement
 

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
868
Ok, Option Explicit is at the beginning of the code. I just didn't post all of it because it is so long and the opening and formatting work fine. This is where it stops.

No I have not got any dim statements. The file that I copied this code from was working just fine and it did not have any dim statements. I am still learning and variables confuse me the most.

I will try Jaymond's suggestion. I actually have about five of these filters in the code. Do I need to include the dim statement before each one, or will once at the start be enough. I know that the code actually turns the autofilter off and back on again each time, but the last when it just turns off.

SO MUCH TO LEARN. SO LITTLE TIME.
but I keep trying
marcia

Just to continue this a bit, if your macros are relatively short and includes no looping (I think recorded macros never do), that declaring variables is not a huge thing. It takes a bit more memory and makes writing the program a bit harder when you have everything as variants (that's what undeclared variables are), but in short code, where execution time is just a couple of seconds or less, it really doesn't matter - unless you execute that several times every minute.

If you decide to begin to learn some "serious VBA" (that is, write program without recording everything), then declaring variables is strongly recommended.

Some of more experienced programmers than me can correct me if I am totally wrong here.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,514
Members
413,995
Latest member
waealu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top