Take last active Row

[jay-excel]

Hello,

I am wondering how I can configure the code below so that only the active rows (those with data in them) will be copied into the new workbook. Any suggestions?


Private Sub CommandButton1_Click()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim SourceRange As Range<o></o>
Dim DestRange As Range<o></o>
Dim DestWB As Workbook<o></o>
Dim DestSh As Worksheet<o></o>
Dim Lr As Long<o></o>
<o></o>
<o></o>
With Application<o></o>
.ScreenUpdating = False<o></o>
.EnableEvents = False<o></o>
End With<o></o>
<o> </o>
If bIsBookOpen_RB("Inside Sales Stats 2009-2010 - master.xls") Then<o></o>
Set DestWB = Workbooks("Inside Sales Stats 2009-2010 - master.xls")<o></o>
Else<o></o>
Set DestWB = Workbooks.Open("\\central\BusSales\Public\Draft Work Orders\Testing Folder - Oxner\Sales Test\Inside Sales Stats 2009-2010 - master.xls")<o></o>
End If<o></o>
<o> </o>
<o></o>
Set SourceRange = ThisWorkbook.Sheets("Sheet1").Range("testrange")<o></o>
<o></o>
Set DestSh = DestWB.Worksheets("2009-December")<o></o>
<o> </o>
<o></o>
<o></o>
Lr = LastRow(DestSh)<o></o>
Set DestRange = DestSh.Range("A" & Lr + 1)<o></o>
<o> </o>
'We make DestRange the same size as SourceRange and use the Value<o></o>
'property to give DestRange the same values<o></o>
With SourceRange<o></o>
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)<o></o>
End With<o></o>
DestRange.Value = SourceRange.Value<o></o>
<o> </o>
DestWB.Close savechanges:=True<o></o>
<o> </o>
With Application<o></o>
.ScreenUpdating = True<o></o>
.EnableEvents = True<o></o>
End With<o></o>
<o></o>
<o> </o>
<o> </o>
<o> </o>
End Sub

 

[Oorang]

It's easier to just copy the whole sheet, then delete the blank rows from the copy. Give this a look: http://www.ozgrid.com/VBA/VBACode.htm