Solved: printing a dynamic named range problem with non contiguous data?

[Simon Lloyd]

Hi all, i am currently trying to print out a range using a dynamic named range like this:
[VBA]
Sub GenReportPrint()
Range("GenRepPrint").PrintOut
End Sub
[/VBA]which works fine and GenRepPrint is defined like this

:=OFFSET('Generated Report'!$A$1,0,0,COUNTA('Generated Report'!$A:$A),7)

however if the range has any blank rows it prints that many less, i.e if i have 22 rows of data that would fall in to the area of the dynamic named range and lets say rows 15 & 16 are blank then it will only print out rows 1 to 20.

If i go to Insert | Names | Define and click in the formula box for this name even though my last row of data would be in row 22 it only highlights rows 1 to 20 because of the 2 blank rows.

How do you create a dynamic named range for non contiguous data?

Regards,
Simon

[lucas]

Alt way to create the named range....hope it helps..it worked for me:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
'advantages over old version
'can move it to different sheets without changing sheet name in code
'handles more rows for new versions of excel
'still works if sheet name is changed
'handles blanks well in data validation use

Dim n As Long

n = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
Me.Range("A1:A" & n).Name = "GenRepPrint"

End Sub[/VBA]

[Simon Lloyd]

Lucas, thanks for that and it looks very useful and i will keep that for future use however i need to determine the actual area eg "A1:Gxx" where it will always be A1 but the following address is variable, i could of course specify the actual range but it will alwas be a count of used rows in column A so column A including blanks = 50 then the range must be A1: & Last Used Column & 50.

Regards,
Simon

[lucas]

Maybe I don't understand????
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
'advantages over old version
'can move it to different sheets without changing sheet name in code
'handles more rows for new versions of excel
'still works if sheet name is changed
'handles blanks well in data validation use

Dim n As Long

n = Me.Range("G" & Me.Rows.Count).End(xlUp).Row
Me.Range("A1:G" & n).Name = "GenRepPrint"

End Sub[/VBA]

[Simon Lloyd]

Lucas thanks for the reply but for it to be universal i could not specify the final column, so i solved it like this:
[VBA]Dim c As String
c = Me.UsedRange.Address
Me.Range(c).Name = "GenRepPrint"
[/VBA]thanks for pushing me in the right direction!

Regards,
Simon