Hi all,
I have a spreadsheet out of which (using a VBA macro) I extract dat
into a comma-delimited text file. below is the main loop construct t
get each cell within each row.

For Each myRecord In Range("A1:A" & Range("A"
Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
<<<processing : pos(cell) = myField.Text 'etc
Next Myfield
End With
Next MyRecord
The problem I am having is that the macro will work on the Activ
(open) sheet when I need it to always use a certain sheet.
Is there a simple solution for that?

Another issue I am having is that there are column filters set up
Sometimes the macro will not dump all opf the records (it would leav
out last 15 or so rows). I am wondering if there is a way to "disable
the filtering in the code of the macro (with the filter "on" th
results are intermittent, with all filters off the results ar
consistent. The picture attached has an example snapshot of the colum
filter.
Thank in advance,
Vitali Barano

Attachment filename: filters.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=56192
--
Message posted from http://www.ExcelForum.com

Hi
something like

'....
Dim wks as worksheet
set wks = activeworkbook.worksheets("special_sheet")
with wks
For Each myRecord In .Range("A1:A" & .Range("A" &
..Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
<<<processing : pos(cell) = myField.Text 'etc
Next Myfield
End With
Next MyRecord
next with


--
Regards
Frank Kabel
Frankfurt, Germany


Hi all,
I have a spreadsheet out of which (using a VBA macro) I extract data
into a comma-delimited text file. below is the main loop construct to
get each cell within each row.

For Each myRecord In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
<<<processing : pos(cell) = myField.Text 'etc
Next Myfield
End With
Next MyRecord
The problem I am having is that the macro will work on the Active
(open) sheet when I need it to always use a certain sheet.
Is there a simple solution for that?

Another issue I am having is that there are column filters set up.
Sometimes the macro will not dump all opf the records (it would leave
out last 15 or so rows). I am wondering if there is a way to
"disable"
the filtering in the code of the macro (with the filter "on" the
results are intermittent, with all filters off the results are
consistent. The picture attached has an example snapshot of the
column
filter.
Thank in advance,
Vitali Baranov

Attachment filename: filters.jpg
Download attachment:
http://www.excelforum.com/attachment.php?postid=561920 ---
Message posted from http://www.ExcelForum.com/

Dim myRecord as Range
Dim myFiled as Range
For Each myRecord In Worksheets("Sheet1") _
.Range("A1").CurrentRegion.Rows

For Each myField In MyRecord.Cells
<<<processing : pos(cell) = myField.Text 'etc
Next Myfield

Next MyRecord


--
Regards,
Tom Ogilvy




"vlbaranov " wrote in message
...
Hi all,
I have a spreadsheet out of which (using a VBA macro) I extract data
into a comma-delimited text file. below is the main loop construct to
get each cell within each row.

For Each myRecord In Range("A1:A" & Range("A" &
Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
<<<processing : pos(cell) = myField.Text 'etc
Next Myfield
End With
Next MyRecord
The problem I am having is that the macro will work on the Active
(open) sheet when I need it to always use a certain sheet.
Is there a simple solution for that?

Another issue I am having is that there are column filters set up.
Sometimes the macro will not dump all opf the records (it would leave
out last 15 or so rows). I am wondering if there is a way to "disable"
the filtering in the code of the macro (with the filter "on" the
results are intermittent, with all filters off the results are
consistent. The picture attached has an example snapshot of the column
filter.
Thank in advance,
Vitali Baranov

Attachment filename: filters.jpg
Download attachment:
http://www.excelforum.com/attachment.php?postid=561920
---
Message posted from http://www.ExcelForum.com/

That worked as a charm .. the macro just pics the Sheet I nee
[B.I.N.G.O]
I still get intermittent results if I have column filters set t
anything other than "All" (see pic attached in the original post)
Still wondering how to make sure macro can see ALL of the row
regardless of filters, etc...
Thanks for help, this is the best forum I have ever been on ... th
only one I have ever been a member of ;] Kudos to all responsibl

--
Message posted from http://www.ExcelForum.com

My answer would solve that, but . . .

--
Regards,
Tom Ogilvy

"vlbaranov " wrote in message
...
That worked as a charm .. the macro just pics the Sheet I need
[B.I.N.G.O]
I still get intermittent results if I have column filters set to
anything other than "All" (see pic attached in the original post)
Still wondering how to make sure macro can see ALL of the rows
regardless of filters, etc...
Thanks for help, this is the best forum I have ever been on ... the
only one I have ever been a member of ;] Kudos to all responsible


---
Message posted from http://www.ExcelForum.com/

VL,

A couple of comments about working in these newsgroups. You should post
your reply to Tom's reply as a reply to his, not to your original post.
That keeps the threading more easy to follow for all if there are many
replies. Also, attachments are stripped.

Hope this helps.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"vlbaranov " wrote in message
...
That worked as a charm .. the macro just pics the Sheet I need
[B.I.N.G.O]
I still get intermittent results if I have column filters set to
anything other than "All" (see pic attached in the original post)
Still wondering how to make sure macro can see ALL of the rows
regardless of filters, etc...
Thanks for help, this is the best forum I have ever been on ... the
only one I have ever been a member of ;] Kudos to all responsible


---
Message posted from http://www.ExcelForum.com/

My appologoies Earl, I was just using the "Post Reply" and the only on
I see is at the end of the thread so thats the one I have used. I wa
actually trying to reply to the post just before Tom's and not too sur
how it got to be after Tom's.
As per Tom's suggestion, I have tried it and don't get get any record
exported, stepped though the code and it seems it does not iterat
rather goes through the loop only once. I will keep trying (especiall
hard with no help .. support yet to install) but in the meanwhile
thank you all for the help and my appologies to the moderators for an
incorrect use of this forum

--
Message posted from http://www.ExcelForum.com

Must be that you are not using the correct objects. With an autofilter
applied, it still picked up every cell.

Sub Tester5()
Dim myRecord As Range
Dim myFiled As Range
For Each myRecord In Worksheets("Sheet1") _
.Range("A1").CurrentRegion.Rows

For Each myfield In myRecord.Cells
Debug.Print myfield.Address
Next myfield

Next myRecord

End Sub

Produced (data in A2:C5)
$A$1
$B$1
$C$1
$A$2
$B$2
$C$2
$A$3
$B$3
$C$3
$A$4
$B$4
$C$4
$A$5
$B$5
$C$5

if you don't want to process the first row:

Sub Tester5()
Dim myRecord As Range
Dim myFiled As Range
Dim rng as Range
set rng = Worksheets("Sheet1") _
.Range("A1").CurrentRegion.Rows
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
For Each myRecord In rng
For Each myfield In myRecord.Cells
Debug.Print myfield.Address
Next myfield

Next myRecord

End Sub

--
Regards,
Tom Ogilvy



"vlbaranov " wrote in message
...
My appologoies Earl, I was just using the "Post Reply" and the only one
I see is at the end of the thread so thats the one I have used. I was
actually trying to reply to the post just before Tom's and not too sure
how it got to be after Tom's.
As per Tom's suggestion, I have tried it and don't get get any records
exported, stepped though the code and it seems it does not iterate
rather goes through the loop only once. I will keep trying (especially
hard with no help .. support yet to install) but in the meanwhile,
thank you all for the help and my appologies to the moderators for any
incorrect use of this forum.


---
Message posted from http://www.ExcelForum.com/

Thanks a ton Tom, I will try the construct on another "tester" simila
to what you had in example above, I must be overlooking something

--
Message posted from http://www.ExcelForum.com

Tom,
it all worked like a charm, it just did not like Worksheets("Sheet1"
but rather wanted to see Worksheets(1). It stopped acting up as soon a
I changed that.
Once again, thank you all for your help, I really apreciate it

--
Message posted from http://www.ExcelForum.com