empty range causes error
 

greetings! i'm pulling data, in the form of dynamic ranges from other tabs,
into a consolidated sheet. sometimes the ranges are empty, either there is
no data or we're waiting for data to be entered, and that seems to be causing
a problem.

part of macro determines how many rows the range is:
range("bloc1").rows.count, so that i know how many rows to drop down when
placing blocs of data from subsequent tabs. however, when the range is empty
"rows.count" seems to give me an error.

any ideas, assistance would be greatly appreciated, thanks

empty range causes error
 

Dim TestRng as range
set testrng = nothing
on error resume next
set testrng = worksheets("somesheetnamehere").range("bloc1")
on error goto 0

if testrng is nothing then
'no range!
else
msgbox testrng.rows.count
end if



mwam423 wrote:

greetings! i'm pulling data, in the form of dynamic ranges from other tabs,
into a consolidated sheet. sometimes the ranges are empty, either there is
no data or we're waiting for data to be entered, and that seems to be causing
a problem.

part of macro determines how many rows the range is:
range("bloc1").rows.count, so that i know how many rows to drop down when
placing blocs of data from subsequent tabs. however, when the range is empty
"rows.count" seems to give me an error.

any ideas, assistance would be greatly appreciated, thanks

--

Dave Peterson

empty range causes error
 

hi dave, thanks for quick reply. this is pretty advanced stuff for me, let
me review and try to write something which will post probably tomorrow,
thanks!


"Dave Peterson" wrote:

Dim TestRng as range
set testrng = nothing
on error resume next
set testrng = worksheets("somesheetnamehere").range("bloc1")
on error goto 0

if testrng is nothing then
'no range!
else
msgbox testrng.rows.count
end if



mwam423 wrote:

greetings! i'm pulling data, in the form of dynamic ranges from other tabs,
into a consolidated sheet. sometimes the ranges are empty, either there is
no data or we're waiting for data to be entered, and that seems to be causing
a problem.

part of macro determines how many rows the range is:
range("bloc1").rows.count, so that i know how many rows to drop down when
placing blocs of data from subsequent tabs. however, when the range is empty
"rows.count" seems to give me an error.

any ideas, assistance would be greatly appreciated, thanks

--

Dave Peterson

empty range causes error
 

good morning, dave. here's code so far, right now it works for each bloc
(there are six), but i'm not sure how to chance the range name, i.e., bloc1,
bloc2, etc. within the a loop. any ideas

Sub consolidated()

Dim testrng As Range
Dim drop As Integer
ActiveSheet.Cells(3, 1).Select

'for counter 1 to 6

Set testrng = Nothing
On Error Resume Next
Set testrng = Range("bloc1")
On Error GoTo errorhandler

If testrng Is Nothing Then
Else:

drop = Range("bloc1").Rows.Count
Range("bloc1").Copy ActiveCell
ActiveCell.Offset(drop, 0).Activate

End If
'Next

errorhandler: Exit Sub

End Sub

empty range causes error
 

Maybe...

Option Explicit
Sub consolidated2()

Dim TestRng As Range
Dim iCtr As Long
Dim DestCell As Range

Set DestCell = ActiveSheet.Range("A3")

For iCtr = 1 To 6

Set TestRng = Nothing
On Error Resume Next
Set TestRng = Range("bloc" & iCtr)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Range Bloc" & iCtr & " wasn't found!"
Exit Sub
Else
TestRng.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(TestRng.Rows.Count)
End If

Next iCtr

End Sub


mwam423 wrote:

good morning, dave. here's code so far, right now it works for each bloc
(there are six), but i'm not sure how to chance the range name, i.e., bloc1,
bloc2, etc. within the a loop. any ideas

Sub consolidated()

Dim testrng As Range
Dim drop As Integer
ActiveSheet.Cells(3, 1).Select

'for counter 1 to 6

Set testrng = Nothing
On Error Resume Next
Set testrng = Range("bloc1")
On Error GoTo errorhandler

If testrng Is Nothing Then
Else:

drop = Range("bloc1").Rows.Count
Range("bloc1").Copy ActiveCell
ActiveCell.Offset(drop, 0).Activate

End If
'Next

errorhandler: Exit Sub

End Sub

--

Dave Peterson

empty range causes error
 

hi dave, that's pretty simple, and noticed that you've eliminated the
ActiveCell which i've read on this forum is something to avoid, thanks! have
a great weekend =D

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub consolidated2()

Dim TestRng As Range
Dim iCtr As Long
Dim DestCell As Range

Set DestCell = ActiveSheet.Range("A3")

For iCtr = 1 To 6

Set TestRng = Nothing
On Error Resume Next
Set TestRng = Range("bloc" & iCtr)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Range Bloc" & iCtr & " wasn't found!"
Exit Sub
Else
TestRng.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(TestRng.Rows.Count)
End If

Next iCtr

End Sub


mwam423 wrote:

good morning, dave. here's code so far, right now it works for each bloc
(there are six), but i'm not sure how to chance the range name, i.e., bloc1,
bloc2, etc. within the a loop. any ideas

Sub consolidated()

Dim testrng As Range
Dim drop As Integer
ActiveSheet.Cells(3, 1).Select

'for counter 1 to 6

Set testrng = Nothing
On Error Resume Next
Set testrng = Range("bloc1")
On Error GoTo errorhandler

If testrng Is Nothing Then
Else:

drop = Range("bloc1").Rows.Count
Range("bloc1").Copy ActiveCell
ActiveCell.Offset(drop, 0).Activate

End If
'Next

errorhandler: Exit Sub

End Sub

--

Dave Peterson

empty range causes error
 

I'm sure that using .select's and .activate's wouldn't cause too much heartache
in your procedure, but they can really make the code difficult to update later
(yep, you'll be making modifications more often than you hoped! <vbg).



mwam423 wrote:

hi dave, that's pretty simple, and noticed that you've eliminated the
ActiveCell which i've read on this forum is something to avoid, thanks! have
a great weekend =D

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub consolidated2()

Dim TestRng As Range
Dim iCtr As Long
Dim DestCell As Range

Set DestCell = ActiveSheet.Range("A3")

For iCtr = 1 To 6

Set TestRng = Nothing
On Error Resume Next
Set TestRng = Range("bloc" & iCtr)
On Error GoTo 0

If TestRng Is Nothing Then
MsgBox "Range Bloc" & iCtr & " wasn't found!"
Exit Sub
Else
TestRng.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(TestRng.Rows.Count)
End If

Next iCtr

End Sub


mwam423 wrote:

good morning, dave. here's code so far, right now it works for each bloc
(there are six), but i'm not sure how to chance the range name, i.e., bloc1,
bloc2, etc. within the a loop. any ideas

Sub consolidated()

Dim testrng As Range
Dim drop As Integer
ActiveSheet.Cells(3, 1).Select

'for counter 1 to 6

Set testrng = Nothing
On Error Resume Next
Set testrng = Range("bloc1")
On Error GoTo errorhandler

If testrng Is Nothing Then
Else:

drop = Range("bloc1").Rows.Count
Range("bloc1").Copy ActiveCell
ActiveCell.Offset(drop, 0).Activate

End If
'Next

errorhandler: Exit Sub

End Sub

--

Dave Peterson


--

Dave Peterson