VB Help
Joel, many many thanks for all your help!!
Here are the answers to your questions.
1) Yes, it is a text file that comes over in one column. I don't do any
text to column formatting.
2) Yes a space between each instance of the found text (Use Start, Agent,
Mgr, Mon1, Tue1,...Sat1) would be great!
3) Yes, that would be fine
4) I think its just wrapping due to space limitations. The actual data has
Mon1 and their corresponding data on the same row.
5) Headers are not required.
"joel" wrote:
I've done this type of programming 1000's of time before. Can yo answer some
additional questions
1) Is the data from a text file? It is easier to read a text file with some
filtering and put the data into the worksheet using a macro. You will have
less problems because the excel importing does some thing you may not want
like but data into two dffiernet rows when they were one row in the source
file
2) Post how you want the results to look. Any spacese beteen tables
(actually pages from your report). The columns where you want the data.
3) The code I'm think of writing will look at each row of the text file.
Only move rows into a workbook if the rows start with the data you need.
4) The results you posted has the lines starting with Mon1, Tue1, ... with
the data on the next line. Is this the way it is in the souce file? Do yo
want the results on 1 row of the worksheet?
5) Do you want any header rows or columns?
"Stan" wrote:
Here is some sample data. Yes, you are correct that I do want to keep the
data above and below the found text of "Use Start" for each instance where
"Use Start" is found.
MU: 790 CS NE LRO CSECSTele IEX TotalView
Page: 1
Agent Preferences Report
Thursday, 04/02/09
Sorting by: Id
All Mgr Values
Include: Default preferences
All date range preferences
Mgr: Barnett, Kymberlyn
Agent: 1405 Jordan, Hiawatha
Seniority: 03/16/01 - 3442 Rank: 0008
DEFAULT PREFERENCES
Last Modified: 03/31/09 07:51:52
Preferred Number Days Work: 1st choice: 2nd: 3rd: 4th: 5th:
6th: 7th: 8th:
Minimum Days Off: 0 Maximum Days Off: 7 Minimum Consecutive Days Off: 0
Preferred Weekly Hour: List
Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours
Hours
1 2 3 4 5 6 7 8 9 10 11
12
Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours
Hours
13 14 15 16 17 18 19 20 21 22 23
24
Fairness Volunteer Levels:
Sun: Request Off
Mon: Request Off
Tue: Request Off
Wed: Request Off
Thu: Request Off
Fri: Request Off
Sat: Request Off
DATE RANGE PREFERENCES
No date range preferences exist.
____________________
MU: 7900 CS NE LRO CSECSTele IEX TotalView
Page: 2
Agent Preferences Report
Thursday, 04/02/09
Sorting by: Id
All Mgr Values
Include: Default preferences
All date range preferences
Mgr: Barnett, Kymberlyn
Agent: 2452 Lacy, Becky
Seniority: 09/16/98 - 2859 Rank: 0002
DEFAULT PREFERENCES
Last Modified: 03/27/09 13:43:09
Preferred Number Days Work: 1st choice: 2nd: 3rd: 4th: 5th:
6th: 7th: 8th:
Minimum Days Off: 0 Maximum Days Off: 7 Minimum Consecutive Days Off: 0
Preferred Weekly Hour: List
Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours
Hours
1 2 3 4 5 6 7 8 9 10 11
12
Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours Hours
Hours
13 14 15 16 17 18 19 20 21 22 23
24
Use Start Times for All Days of the Week: No
Start Start Start Start Start Start Start Start Start Start
Start Start Start Last
Day Pref Range 1 2 3 4 5 6 7 8 9
10 11 12 Modified
Mon1 Early-Late
03/27/09 16:05:14
Tue1 Early-Late
03/27/09 16:05:14
Thu1 Early-Late
03/27/09 16:05:14
Fri1 Early-Late
03/27/09 16:05:14
Fairness Volunteer Levels:
"joel" wrote:
It is not clear exactly what you want. It sound like you have a group of
tables on one worksheet that has blank rows between each table. In each
table there is a row that contains "Use Start". You only want to keep
certain rows in the table above and below "Use Start" THIS IS CORRECT!
When you delete rows the easiest way is to start at the end of the worksheet
and go up the worksheet to row 1. This way when you delete a row you can
still decrement by one to get to the next row. When you move in the other
direction you need to add one row when you don't delete and don't add one row
when you do delete.
Sub DeleteRows()
Keep1 = Array("Mgr", "Agent")
Keep2 = Array("Mon1", "Tue1", "Wed1", "Thur1", "Fri1", "Sat1")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = LastRow
AboveUseStart = True
Do While RowCount = 1
Select Case Range("A" & RowCount)
Case "": AboveUseStart = True
Case "Use Start": AboveUseStart = False
Case Else
If AboveUseStart = True Then
SearchStr = Keep2
Else
SearchStr = Keep1
End If
Found = False
For Each itm In SearchStr
If Range("A" & RowCount) = itm Then
Found = True
Exit For
End If
Next itm
If Found = False Then
Rows(RowCount).Delete
End If
End Select
RowCount = RowCount - 1
Loop
End Sub
"Stan" wrote:
I'm new to coding so any help you could provide would be greatly appreciated.
I'm wanting to find rows in a spreadsheet that contains the words "Use Start".
If found, I need to find rows above the found text that include the text
"Mgr." and "Agent" and below the text that includes "Mon1",
"Tue1",....."Sat1".
Then loop the code to continue to find all the rows that include "Use Start"
and the rows above and below that have Mgr., Agent, Mon1, Tue1,....Sat1.
All other rows in the spreadsheet would be deleted.
Here is some code that I started on to find rows that include "Use Start"
but it doesn't work.
Sub cleanup2()
Dim rng As Range
Set rng = Range("A:A").Find(What:="Use Start", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
Do
Row.Delete
Loop While rng Is Nothing
End If
End Sub
|