Testing dynamic range for values
 

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

Testing dynamic range for values
 

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
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Testing dynamic range for values
 

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" wrote:

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
---------------
Jacob Skaria


"Ken Warthen" wrote:

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

Testing dynamic range for values
 

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
---------------
Jacob Skaria


"Ken Warthen" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Ken Warthen" wrote:

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