Dynamic Range in a Macro Problem

[Willmonbo]

My issue is with a number of tasks all written in one macro, but one task refers to a dynamic range that the macro needs to copy and past elsewhere in the workbook, but on occasion that dynamic range won't have any data and therefore equal to no range to copy so the code gets stuck, how do I get the code to continue running the rest of the tasks its designed to do.

 

[Smitty]

How about posting the code that you have now and indicate where the dynamic range might be empty?

It could be as simple as doing a count on the range and if the count = 0 then skip to the next step.

 

[Willmonbo]

that would definitely help, I just don't know how to write code at all.

so the problem code is:

Sheets("Database").Select
Range("DynamicHoldingTank").Select
Selection.Copy

and it's not a problem unless the dynamic range evaluates to zero lines which could happen on occasion not in error but by design.

 

[Smitty]

This should get you started:

<font face=Calibri>    <SPAN style="color:#00007F">With</SPAN> Sheets("Database").Range("DynamicHoldingTank")<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Application.WorksheetFunction.CountA(Range("DynamicHoldingTank")) = 0 <SPAN style="color:#00007F">Then</SPAN><br>            .Copy <SPAN style="color:#007F00">' Paste destination?</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Willmonbo]

thanks so much, but it now gets stuck at the "with" statement.

With Sheets("Database").Range("DynamicHoldingTank")

since the range doesn't evaluate to anything.

 

[Smitty]

That shouldn't make any difference; you can reference a range regardless of its contents. How have you defined that named range?

 

[Willmonbo]

with the offset function

=OFFSET(HoldingTank,0,0,Database!$Y$3,6)

 

[Willmonbo]

Just to start over in describing my ordeal, I am having trouble with a macro that has as one of its tasks to copy a specified dynamic range and paste it in a different location in the workbook.

the problem though is that since sometimes there is no data in the dynamic range to be copied and in that scenario I need the macro to still continue to execute the rest of the tasks, so I added the following code:

Dim nm As Name
On Error Resume Next
Set nm = Workbooks("MyWorkbook").Names("DynamicHoldingTank")
If Not nm Is Nothing Then
'it exists!!

Sheets("DataBase").Select
Range("DynamicHoldingTank").Select
Selection.Copy
Application.Goto Reference:="R1048576C53"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(2, 10).Range("A1").Select
Else
'it doesn't!

End If

I also tried this different code:


With Sheets("Database").Range("DynamicHoldingTank")
If Not Application.WorksheetFunction.CountA(Range("DynamicHoldingTank")) = 0 Then
.Copy
End If
End With

to my dismay all the code included in the "IF" argument doesn't execute properly, the rest of the macro executes nicely, but the code inside the IF argument does not.

I hope I made myself clear and would greatly appreciate someone helping me.

 

[Willmonbo]

Mr. Smitty is the Man,

the culprit turned out to be the way the macro recorder writes code, that I tried to emulate and that just wouldn't work, so Mr. Smitty came to the rescue and suggested this code that works like a charm.

If Sheets("Databse").Range("Y3") > 0 Then
With Sheets("DataBase")
.Range("DynamicHoldingTank").Copy
.Cells(Rows.Count, "BA").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End If

Thanks again Smitty for being such a great help.