Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Empty cell range and error 91 | Excel Programming | |||
#Empty error | Excel Discussion (Misc queries) | |||
Trapping error from Empty Dynamic Named Range | Excel Programming | |||
Finding next empty empty cell in a range of columns | Excel Programming |