Last Row checking all columns

[ABU_HASAN]

Sub ArtReport()
With Sheets("art report")
.Unprotect
lRow = Worksheets("art report").Cells(Worksheets("Invoice").Rows.Count, 1).End(xlUp).Row
lRow = lRow + 1
'Inv no
Sheets("Invoice").Range("I8").Copy
Worksheets("art report").Range("A" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'date
Sheets("Invoice").Range("I9").Copy
Worksheets("art report").Range("B" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'customer
Sheets("Invoice").Range("A9").Copy
Worksheets("art report").Range("C" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'art
Sheets("Invoice").Range("F16,F18,F20,F22,F24,F26,F28,F30,F32,F34").Copy
Worksheets("art report").Range("D" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'color
Sheets("Invoice").Range("G16,G18,G20,G22,G24,G26,G28,G30,G32,G34").Copy
Worksheets("art report").Range("E" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'pcs
Sheets("Invoice").Range("H16,H18,H20,H22,H24,H26,H28,H30,H32,H34").Copy
Worksheets("art report").Range("F" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'sqft
Sheets("Invoice").Range("I16,I18,I20,I22,I24,I26,I28,I30,I32,I34").Copy
Worksheets("art report").Range("G" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'amount
Sheets("Invoice").Range("L16,L18,L20,L22,L24,L26,L28,L30,L32,L34").Copy
Worksheets("art report").Range("H" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
'type
Sheets("Invoice").Range("F8").Copy
Worksheets("art report").Range("I" & lRow).PasteSpecial xlPasteValuesAndNumberFormats
.Protect
End With
End Sub

I use the above code to transfer data from my worksheet "Invoice" to worksheet "art master" but while transferring data the data is being overwritten I want that the last row in "art master" is determined after checking all columns of "art master". At the moment with above code it is taking last row only with 1st column of "art report"
Kindly help

 

[Rick Rothstein]

I want that the last row in "art master" is determined after checking all columns of "art master". At the moment with above code it is taking last row only with 1st column of "art report"

Consider the following for finding the last used row or last used column where I reference the ActiveSheet for generality (change it to your actual sheet reference as needed)...

[table="width: 500"]
[tr]
	[td][B][SIZE=4]For last value[/SIZE][/B]
---------------------------
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
              SearchDirection:=xlPrevious, LookIn:=xlValues, SearchFormat:=False).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, LookIn:=xlValues, SearchFormat:=False).Column

or, more compactly...

LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td][B][SIZE=4]For last value or formula[/SIZE][/B]
-------------------------------------------
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
              SearchDirection:=xlPrevious, LookIn:=xlFormulas, SearchFormat:=False).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, LookIn:=xlFormulas, SearchFormat:=False).Column

or, more compactly...

LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Row
LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column[/td]
[/tr]
[/table]

A note about those headings... the ones labeled "For last value" means it will return the last used row and column for a displayed value (whether a constant or one from a formula) even if there are formulas after those cells displaying the empty string (""); whereas those labeled "For last value or formula" returns the last cell with either a value or a formula even if that formula is displaying the empty string ("").

 

[MARK858]

Try replacing

lRow = Worksheets("art report").Cells(Worksheets("Invoice").Rows.Count, 1).End(xlUp).Row
 lRow = lRow + 1

with

lRow = Worksheets("art report").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1

 

[ABU_HASAN]

Try replacing

lRow = Worksheets("art report").Cells(Worksheets("Invoice").Rows.Count, 1).End(xlUp).Row
 lRow = lRow + 1

with

lRow = Worksheets("art report").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1

The above code worked THANKS !!!!!