delete all non-empty rows

[ysherriff]

Can someone help me with a more efficient way to use the last row statement. I keep havign to change the destination cell number everytime if i use a different workbook or parameter. The issue i am having is highlighed in red.

LastRow = ActiveSheet.Range("D94").End(xlUp).Row
If LastRow > 1 Then
sh.Range("B9:L" & LastRow).Copy

'activate generator workbook
currentWB.Activate

'activate master worksheet
TargetSh.Activate

TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 10)
dataWB.Close False


I have attached the spreadsheet. The macro name is mod_consolidate

Thank you

 

[ysherriff]

I forgot the attachment

 

[sn152]

Hi,

Try this.

lastRow = Activesheet.Cells(.Rows.Count, "A").End(xlUp).Row

 

[Deepak]

If Still having the issue then pls here!!!

http://www.rondebruin.nl/win/s9/win005.htm

 

[p45cal]

There is no red highlighting!

Untested, but try changing:

sh.Range("B9:L" & LastRow).Copy

to:

set SourceRng = sh.Range("B9:L" & LastRow)
SourceRng.copy

later, change:

Set DestCell = DestCell.Offset(LastRow - 10)

to:

Set DestCell = DestCell.Offset(SourceRng.rows.count)

As an aside, again untested, you might be able to condense:

'activate generator workbook
currentWB.Activate

'activate master worksheet
TargetSh.Activate

TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to one line:

DestCell.PasteSpecial Paste:=xlPasteValues

The main unknown I have is the variability of the line:
sh.Range("B9:L" & LastRow).Copy above, but in the file you attached:
sh.Range("B6:N" & LastRow).Copy

Two differences; the width and the start row. Are the source sheets all different? Is there someway the start row and width of the range to copy can be determined from the sheet itself? There may be a header cell we could look for? Maybe width could be determined by a .End(xlToRight) or .End(xlToLeft). Perhaps upload 2 samples of source sheets so that we can look for a way to do this.