Check if cells/rng is blank before executing next step

[julhs]

I can’t get my currant testing code to skip a section of code IF a given range is Blank/zero.
Currant my testing code is not skipping the section of code that clears that range, and then move onto the next section of code if THAT range is "Blank"
As my testing code stands, I'm getting the MsgBox regardless whether the range is "Blank" or clearly contains a value
My intention is:
IF range “fUsedRng = sht.Range("AN" & DestRow + 2)" is blank/zero, a MsbBox is displayed.
It will then SKIP the next section that “Clears” that range and move onto rest of code.

Hopefully the below contains enough code to understand what I’m trying to do!!!
It is not my actual testing, just an outline
Essentially this is about whether my logic is correct and where my code has/ is falling short, NOT about an ultimate resolving code, (I won’t learn as much from that)

Sub IfRangeBlank_ContinueToNextSection()
Dim sht As Worksheet
Dim rng As Range
Dim DestRow As Long
Dim fUsedRng As Range

Set sht = ThisWorkbook.ActiveSheet
Set fUsedRng = sht.Range("AN" & DestRow + 1)
If fUsedRng = 0 Then
MsgBox "FUsedRng is empty skip next section of code and GO TO next step/section of code", vbOKOnly
GoTo Line1:
End If
'-----------------------
'Skip this section of code that clears "sht.Range("AN" & DestRow + 2)"
IF "fUsedRng"   is BLANK ("") or is not 0
'-----------------------------------------------
Line1:
On Error Resume Next
'Continue with rest of code

 

[MARK858]

Try for the blanks (if they are truly Blank and not formulas returning "")....

If WorksheetFunction.CountA(fUsedRng) = 0 Then

 

[MARK858]

If you want to test if every cell in the range isn't blank or zero then top of my head I can't think of an option without testing each cell, something like

    Dim sht As Worksheet
    Dim fUsedRng As Range, MyCell As Range
    Dim i As Integer

    Set sht = ThisWorkbook.ActiveSheet
    Set fUsedRng = sht.Range("A1:a14")           'change range to suit

    i = 0
  
    For Each MyCell In fUsedRng
        If MyCell.Value <> "" And MyCell.Value <> 0 Then i = i + 1
    Next
  
    If i <> 0 Then
        'do whatever you want to do if the cells in the range aren't blank or zero
    Else
        MsgBox "FUsedRng is empty skip next section of code and GO TO next step/section of code", vbOKOnly
        GoTo Line1:
    End If

 

[julhs]

Sorry for delay in getting back to you.

Had tried to get “If WorksheetFunction.CountA(fUsedRng) = 0 Then”
to run but had failed, your post #2 confirmed that is should.

Problem was I hadn’t declared my variables correctly for it; was I using “Dim As Long” and not “Dim As Interger”. (Not present in initial post)
Offending declarations:-
Dim LrwD As Long, changed to Dim LrwD As Integer
Dim Frow As Long, changed to Dim Frow As Interger
Changing those meant that my using, “Set ClearRng = sht.Range("AL" & Frow + 1 & ":AN" & LrwD + 1)” WORKED for me.
Needed to use that because the range to test for “EMPTY” is a dynamic range, so simply using a fixed range wasn’t going to work for me.

Many thanks for your help.

 

[MARK858]

Problem was I hadn’t declared my variables correctly for it; was I using “Dim As Long” and not “Dim As Interger”. (Not present in initial post)

That should make no difference as they both apply to whole numbers (in fact Long covers a wider range than Integer does. It could make a difference if it was the other way around in certain code but not this one)

 

[julhs]

Based on your commented I just switched back to “Long” and things worked. So there must have been something else I changed when switching to Integer that ultimately stopped the “1004” compile errors.
Thanks for the help

 

[MARK858]

You're welcome