Finding last used cell in Col A in secondary workbook

[ckelley1020]

I have a wkbk open I am running a macro from. I am trying to look at a second wkbk and find the last used cell in column A. I am using this code and it is not working. Any ideas what I am doing wrong?

lRow2 = wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

a similar line of code that works fine in the active workbook (1)

Thanks for any help!

 

[Michael M]

Have you set the 2nd workbook ?

set wb2=C:\Temp\anotherworkbook.xls

 

[ckelley1020]

Yes. Everything else works but this one line. Very strange. If I leave it as shown I get a run time error "1004" - Application defined or object defined error

as I said, same code for wb1(active I am running macro from) works fine

 

[Michael M]

Maybe you should post the rest of your code

 

[ckelley1020]

Sub DeleteDuplicates()
'Checking if second workbook is open then macro won't proceed
If bIsWorkBookOpen("WKBK2.xls") = False Then 'Change WKBK2.xls i.e. Second Workbook to suit
MsgBox "Second WorkBook Is Not Open!!!" & vbCr & _
"Open It and then Rerun this macro!!!"
Exit Sub
Else
'Avoiding screen flicker
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb2 = Workbooks("WKBK2.xls") 'Change WKBK2.xls i.e. Second Workbook to suit
lRow1 = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lRow1 'Considering 1st row as header row. Change to suit
s1 = Range("A" & i).Value2 & Range("B" & i).Value2 & Range("C" & i).Value2 & _
Range("D" & i).Value2 & Range("E" & i).Value2 & Range("F" & i).Value2 & Range("G" & i).Value2

lRow2 = wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

'lRow2 = 6

For j = lRow2 To 2 Step -1 'Considering 1st row as header row. Change to suit
s2 = wb2.Sheets(1).Range("A" & j).Value2 & _
wb2.Sheets(1).Range("B" & j).Value2 & _
wb2.Sheets(1).Range("C" & j).Value2 & _
wb2.Sheets(1).Range("D" & j).Value2 & _
wb2.Sheets(1).Range("E" & j).Value2 & _
wb2.Sheets(1).Range("F" & j).Value2 & _
wb2.Sheets(1).Range("G" & j).Value2
If s1 = s2 Then
wb2.Sheets(1).Rows(j).Delete
End If
Next j

Next i
'Resetting to Excel Defaults
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End If
End Sub
Public Function bIsWorkBookOpen(sWBName As String) As Boolean
'Thanks to Excel 2007 VBA Programmer's Reference / Chapter 3
On Error Resume Next
Set wb2 = Workbooks(sWBName)
If Not wb2 Is Nothing Then
bIsWorkBookOpen = True
End If
End Function

 

[Michael M]

Without using any data the code seems to work fine while WKBK2 is open.
If I close WKBK2 the code doesn't give me the error MsgBox
Is "WKBK2.xls" the correct filename ?

 

[ckelley1020]

Yep.that's the name. Wit data it fails every time.

 

[Michael M]

Yeah, sorry.
I'm still having issues with the fact that the code doesn't show the MsgBox when WKBK2 isn't open...
Also,
couldn't these lines

For i = 2 To lRow1 'Considering 1st row as header row. Change to suit
s1 = Range("A" & i).Value2 & Range("B" & i).Value2 & Range("C" & i).Value2 & _
Range("D" & i).Value2 & Range("E" & i).Value2 & Range("F" & i).Value2 & Range("G" & i).Value2

lRow2 = wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

'lRow2 = 6

For j = lRow2 To 2 Step -1 'Considering 1st row as header row. Change to suit
s2 = wb2.Sheets(1).Range("A" & j).Value2 & _
wb2.Sheets(1).Range("B" & j).Value2 & _
wb2.Sheets(1).Range("C" & j).Value2 & _
wb2.Sheets(1).Range("D" & j).Value2 & _
wb2.Sheets(1).Range("E" & j).Value2 & _
wb2.Sheets(1).Range("F" & j).Value2 & _
wb2.Sheets(1).Range("G" & j).Value2

be reduced to

For i = 2 To lRow1 'Considering 1st row as header row. Change to suit
s1 = Range("A" & i & ":G" & i).Value
lRow2 = wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

For j = lRow2 To 2 Step -1 'Considering 1st row as header row. Change to suit
s2 = wb2.Sheets(1).Range("A" & j & ":G" & j).Value

 

[ckelley1020]

How do I add an attachement to the post? I'll give you my 2 wkbks with data in them. All of the code works fine as is, with the exception of only this one line. Hopefully having the actual stripped down data would help to solve. This is frustrating that I can't get this to work!!!!!!Arghhhhhhhhhhhhhh

 

[Michael M]

I'm obviously missing something, hopefully this BUMP will get you back to the top of the list and others may be able to help out !
You can't post an attachment to this Forum.
You will have to use a secondary app like Mediafire and then provide the link to your workbook there.