Desperate:Help on VB to Excel

[joey o.]

Help! I'm on my first real assignment without any resources in work. I need 2 things:

1. A way to check for 2 empty rows in MSExcel
'I'm trying this (It doesn't work!)
For I = 4 To 100
If XL.Range("A" & I : "J" & I) = "" Then
MsgBox "end found"
Exit Sub
End if
Next

2. A way to go through the worksheets collection without physically typing out all 17+ tab names so that I can do some actions to each one, then move to the next sheet and do some more actions.

Thanks in advance, any help is appreciated, I'm "temp to perm" on this job. If I can't do this I may be not have a chance at the "perm" end of this job. Please Help.

[Al Smith]

Hi,
There's better ways of doing this but a quick fix is :

If XL.Range("A" & trim(str(I)) : "J" & trim(str(I)) = "" Then

What happens is that you range string has spaces in front of I. e.g. XL.Range ("A 1:J 1)

Al.

[scottr]

Hi, there is a great website that has the code to check for empty row and lots of other stuff

http://www.j-walk.com/ss/excel/index.htm

[joey o.]

Thank you! I may become part of the permanent work force yet. Any other suggestions are still welcome, I'm going to try this stuff now!
THanks Again!

[joey o.]

Oh-No! This doesn't work. VB complains it wants a list seperator. I even tryed using the Keycode for the colon and still ended up with the error. I only have two hours to come up with something. Help, Please!

If .Range("A" & (trim(str(I))& chr(58)) & ("J" & (trim(str(I)))) = "" Then BlahBlah

[LG]

Hi, there.
Scottr gave you a good link. Try this, may give you some idea how to do it:
http://www.j-walk.com/ss/excel/tips/tip30.htm


For the second part, it sounds like you need to create a macro.

Larisa

[Frans C]

Or if you can't get that to work, you can try this.

Sub GetEmptyRows()
Dim LastRow As Long
Dim r As Long
    LastRow = XL.ActiveSheet.UsedRange.Row - 1 + XL.ActiveSheet.UsedRange.Rows.Count
    XL.ScreenUpdating = False
    For r = 1 To LastRow
        If XL.CountA(XL.ActiveSheet.Rows(r)) = 0 Then
            MsgBox r
        End If
    Next r
End Sub

About your second question, you can simply loop though the sheets collection in the workbook.

Dim SH As Excel.Worksheet
For Each SH In XL.ActiveWorkbook.Sheets
    MsgBox SH.Name
Next

[Al Smith]

If XL.Range("A" & trim(str(I)) & ":J" & trim(str(I)) = "" Then

Al.

[joey o.]

Thanks Guys! I guess I just got a little panicky. The link was good. I think I was looking for completed code though instead of the obvious ".LastRow" with some tweeking. I manually wrote the collection of sheet names to scroll through but I'd love to find out how to just refrence the worksheets collection in the future. Once again you guys have saved my butt. I hope someday I can do the same for you. Thanks!
Joey O.