Export TXT from wksht selected in ComboBox
 

I have 30+ worksheets that contain formula values for export (as space
delimited txt files). w/ a UserForm I want the user to choose 1 worksheet
from a ComboBox and press 1 of 2 cmd buttons

cmd button 1 - go to sheet
cmd button 2 - export txt file - to location of choice

--- tricky part
All worksheets have varying # of rows and I'd like to avoid exporting rows
like the 2nd row below:
---
M-SYMB-P100 92 CONTINUOUS 0.50
M- #N/A #N/A #N/A
---
Thanks for any help
--
JS

Export TXT from wksht selected in ComboBox
 

I will assume that you have some VBA experience if you have gotten this far
on a project like this. You can try a couple of things.

Option 1
Find a column in the worksheet that always has data. That is your constant.
Loop through all rows in the worksheet where that cell is not empty. Delete
the rows that have bad data based on the evaluation of another column.
Export the data when all bad rows have been deleted.

Option 2
If you can't delete rows, your coding get's a bit more complicated. You can
write each row to a file individually, eliminating the bad rows.

Option 3
Another option that might work if you can't delete records is to make a
temporary copy of the sheet and delete the rows you don't want to export.
Export the data then delete the sheet.

Let me know if you need further explanation.
--
Happy Coding,

Scott


"jhs626" wrote:

I have 30+ worksheets that contain formula values for export (as space
delimited txt files). w/ a UserForm I want the user to choose 1 worksheet
from a ComboBox and press 1 of 2 cmd buttons

cmd button 1 - go to sheet
cmd button 2 - export txt file - to location of choice

--- tricky part
All worksheets have varying # of rows and I'd like to avoid exporting rows
like the 2nd row below:
---
M-SYMB-P100 92 CONTINUOUS 0.50
M- #N/A #N/A #N/A
---
Thanks for any help
--
JS

Export TXT from wksht selected in ComboBox
 

Scott, thanks. I've unintentionally tricked you into thinking I know VBA.
just enough to be dangerous... but trying to learn.

My ultimate goal is to provide a form where the user can change a few
"control" variables, update the dependent wrkshts, and choose which one gets
exported to space-delimited txt file. One step-at-a-time...


Option 1 would work fine. What might the code be if I wanted to delete all
rows where any cell in column "C" contained either "#N/A" or "0"?

Thanks for your help.
JS
---
A little more detail may help at this point...
I have 10 XLS files to process. In ea. XL there is 1 wrksht (control) that
contains the core of variable data (manually changed by user). The remaining
wrkshts append additional data (automated) to the core data and apply other
variants as they are processed.
The manner in which these wrkshts are processed is fr top row down - using
autofill - therefore deleting "bad rows" at the bottom is fine.

The intent is to have the user add/remove core data (row-by-row) in the
"control" wrksht and then update (using a simple macro) the remaining
workshts.

--- worksheet update ---
Sub
Range("A1:J1").Select
Selection.AutoFill Destination:=Range("A1:J125")
Range("A1:J125").Select
End Sub
---
The number of columns remains the same... the rows varies. I've just
extended it to 125 to make sure I update everything.

JS

"jhs626" wrote:

I have 30+ worksheets that contain formula values for export (as space
delimited txt files). w/ a UserForm I want the user to choose 1 worksheet
from a ComboBox and press 1 of 2 cmd buttons

cmd button 1 - go to sheet
cmd button 2 - export txt file - to location of choice

--- tricky part
All worksheets have varying # of rows and I'd like to avoid exporting rows
like the 2nd row below:
---
M-SYMB-P100 92 CONTINUOUS 0.50
M- #N/A #N/A #N/A
---
Thanks for any help
--
JS

Export TXT from wksht selected in ComboBox
 

This function will delete rows that meet the condition you specified:

Sub DeleteInvalidRows(ws As Worksheet)
Dim lngActiveRow As Long
Dim rng As Range


'Set the starting point in the worksheet to go through
'This should be the first cell in a column of continuous data, no breaks
Set rng = ws.Range("A1")

Do Until IsEmpty(rng) 'Loop through all records until you
reach an empty row
lngActiveRow = rng.Row
'check the cell three cells over
If rng.Offset(0, 2) = "#N/A" Or rng.Offset(0, 2) = "0" Then
rng.EntireRow.Delete 'Delete the row

Set rng = ws.Cells(lngActiveRow - 1, 1) 'After deleting the row
the range
'variable becomes
invalid so we need
'to reset it. The -1
sets it back to
'previous row so the
next statement
'can still move it
forward.

End If
Set rng = rng.Offset(1, 0) 'Move to the next row
Loop
End Sub

Here is how you use it:

DeleteInvalidRows ActiveSheet

or

DeleteInvalidRows Worksheets("Sheet1")

or

Create a loop for all worksheets

For Each ws in ThisWorkbook
DeleteInvalidRows ws
Next

I don't know if this helps or not. Feel free to email me a sample if you
have one with non-confidential data. If it is not too difficult, I will see
what I can do to get you started.

--
Happy Coding,

Scott


"jhs626" wrote:

Scott, thanks. I've unintentionally tricked you into thinking I know VBA.
just enough to be dangerous... but trying to learn.

My ultimate goal is to provide a form where the user can change a few
"control" variables, update the dependent wrkshts, and choose which one gets
exported to space-delimited txt file. One step-at-a-time...


Option 1 would work fine. What might the code be if I wanted to delete all
rows where any cell in column "C" contained either "#N/A" or "0"?

Thanks for your help.
JS
---
A little more detail may help at this point...
I have 10 XLS files to process. In ea. XL there is 1 wrksht (control) that
contains the core of variable data (manually changed by user). The remaining
wrkshts append additional data (automated) to the core data and apply other
variants as they are processed.
The manner in which these wrkshts are processed is fr top row down - using
autofill - therefore deleting "bad rows" at the bottom is fine.

The intent is to have the user add/remove core data (row-by-row) in the
"control" wrksht and then update (using a simple macro) the remaining
workshts.

--- worksheet update ---
Sub
Range("A1:J1").Select
Selection.AutoFill Destination:=Range("A1:J125")
Range("A1:J125").Select
End Sub
---
The number of columns remains the same... the rows varies. I've just
extended it to 125 to make sure I update everything.

JS

"jhs626" wrote:

I have 30+ worksheets that contain formula values for export (as space
delimited txt files). w/ a UserForm I want the user to choose 1 worksheet
from a ComboBox and press 1 of 2 cmd buttons

cmd button 1 - go to sheet
cmd button 2 - export txt file - to location of choice

--- tricky part
All worksheets have varying # of rows and I'd like to avoid exporting rows
like the 2nd row below:
---
M-SYMB-P100 92 CONTINUOUS 0.50
M- #N/A #N/A #N/A
---
Thanks for any help
--
JS