Testing dynamic range for values

[Ken Warthen]

I have a dynamic range in an Excel worksheet. In a VBA module I have a
routine that exports the values within the dynamic range to a CSV file. How
can I test to make sure there is at least one value (record) in the dynamic
range?

Ken

 

[Jacob Skaria]

Dim rngTemp As Range
Set rngTemp = Range("A1:J10")

If WorksheetFunction.CountA(rngTemp) = 0 Then
'No records
Else
'Write to csv
End If


If this post helps click Yes

 

[Ken Warthen]

Jacob,

Thanks for your help. I tried the following but am getting an error on the
Set statement saying "Method 'Range' of object'_Global' failed."

Ken


Dim rng As Range

'clear any CSV worksheet data
With ThisWorkbook.Worksheets("CSV")
Set rng = Range("CSVExportRange")
If WorksheetFunction.CountA(rng) <> 0 Then
For Each Cell In Range(rng)
If Cell.Value <> "" Then
Cell.Value = ""
End If
Next Cell
End If
End With

 

[Jacob Skaria]

Dot (.) missing. Try the below

Dim rng As Range

'clear any CSV worksheet data
With ThisWorkbook.Worksheets("CSV")
Set rng = .Range("CSVExportRange")
If WorksheetFunction.CountA(rng) <> 0 Then
For Each Cell In Range(rng)
If Cell.Value <> "" Then
Cell.Value = ""
End If
Next Cell
End If
End With

If this post helps click Yes