If Nothing Then VBA

[kmmsquared]

Hi!

I am running into a problem with my code below. When Range("AB2:AB" & lngLastRow) is nothing, Column AB doesn't delete. How should I fix this? Thanks!

Sub Check()
 
Dim lngLastRow As Long
 
With Sheets("B")
lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set Check = Range("AB2:AB" & lngLastRow)

Columns("AB:AB").Copy
Columns("AB:AB").PasteSpecial Paste:=xlPasteValues
Columns("AB:AB").AutoFilter Field:=1, Criteria1:="=UD", Operator:=xlOr, Criteria2:="="
If Not Check Is Nothing Then Check.SpecialCells(xlCellTypeVisible).ClearContents
Columns("AB:AB").AutoFilter
        
[COLOR=red]If Check Is Nothing Then
[/COLOR][COLOR=red]Columns("AB:AB").Delete[/COLOR]
[COLOR=red]Else
[/COLOR]    Columns("AB:AB").AutoFilter Field:=1, Criteria1:="<>"
    On Error Resume Next
    Range("AB2:AB" & lngLastRow).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 22
 
    Columns("AB:AB").AutoFilter
End If
End With
 
End Sub

 

[Joe4]

A few problems.

How can it be nothing? It looks like it is a range. Do you mean no data in the range?

Also, it is not a good idea to use a variable in your sub procedure that has the same name as the sub procedure. That could confuse VBA, because then anytime you use "Check" in your code, it might have a hard time trying to figure out if you are calling the "Check" procedure, or the "Check" range variable.

 

[kmmsquared]

I do mean no data in range...that is what I was going for, how do I do that?

I will fix the variable/name of sub too!

 

[VoG]

It cannot be Nothing - you have just assigned a range to it. Maybe

If WorksheetFunction.CountA(check) = 0 Then

 

[Joe4]

Here is an example of how you can check to see if your range is empty:

    Dim myRange As Range
    Dim myRangeEmpty As Boolean

'   Set your range here    
    Set myRange = Range("A1:A4")
 
'   Check to see if range is empty (set myRangeEmpty variable equal to true/false)
    If Application.WorksheetFunction.CountBlank(myRange) = myRange.Cells.Count Then
        myRangeEmpty = True
    Else
        myRangeEmpty = False
    End If

 

[kmmsquared]

That worked...thanks!