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
|