-
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.
-
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.
-
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
-
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!
-
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
-
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
-
Or if you can't get that to work, you can try this.
Code:
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.
Code:
Dim SH As Excel.Worksheet
For Each SH In XL.ActiveWorkbook.Sheets
MsgBox SH.Name
Next
-
I missed some quotes.
If XL.Range("A" & trim(str(I)) & ":J" & trim(str(I)) = "" Then
Al.
-
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.