Pleas help with LastRow vba code 1

[tcolan]

Hello,

I am attempting to create some vba code that will copy a range of rows into another worksheet. Here are the details:

1. I want to copy all of the rows that have data in the "Defect Macro" worksheet. This is the range B10:C? (?-because I don't know how many rows)

2. Paste this information into the "Current Defect Log" worksheet starting with the cell A10.

3. As I re-run the function I want new rows that are from the "Defect Macro" tab to be placed below the last row with data on on the "Current Defect Log" tab.

Now here's my problem. Everything works great and exactly how I want it except something is wrong with my LastRow function. The way the spreadsheet is set up is that I want the data to paste into columns A & B on the "Current Defect Log" tab. I also have formulas that will then use this data in cells C & D of the "Current Defect Log" tab. For some reason the LastRow formula is looking across the whole row before it pastes and not just the columns that matter (A & B).

So in other words if I am on row 10 and have columns A & B empty but a formula in column C, then the macro will not paste the info in columns A & B of that row. Instead it would paste into A & B of row 11 (if row 11 had nothing in columns A, B & C)

I am not very well schooled in VBA, so any help would be greatly appreciated.

Thanks for your help!!!
Tom





Sub CopyOneArea()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("2 Current Defect Log")) + 1
Set sourceRange = Sheets("Defect Macro").Range("b10:c2000")
Set destrange = Sheets("2 Current Defect Log").Range("A" & Lr)
sourceRange.Copy destrange
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[TonyJollans]

Hi tcolan,

What your LastRow routine does, which is most times what you would want, is start at the bottom right of the range (the whole worksheet in this case because of sh.Cells) and look from right to left along each row working upwards until it finds a non-empty cell; it then returns the row number of that cell.

What you want to do is only search columns A and B instead of the whole sheet, so you need to limit the search to those two columns. instead of ..

[purple]LastRow = sh.Cells.Find(What:="*", _
[/purple]

you want to use ..

[blue]LastRow = sh.Range("A:B").Find(What:="*", _
[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[tcolan]

Tony,

Thanks so much. Exactly what I needed!!!!

Have a good one!!!
Tom