Can you help me in understanding some code

[walt1122]

Hello agin All, I stumbled across some code and thought I could modify and use it in my application. But I don't seem to be able to get it to work. I want to be able to have this program open up a text file the user picks and sort out any "blank" rows and any rows that do not have, in this case the "AFF" in the row and delete them as well. I can get it to display the open dialog box and open the file and copy it to a template. But when the RemoveGarbageTowers runs it deletes everything. Can somebody help me find the problem and maybe tell me what the "ElseIf .Cells(Row, 3) <> "AFF" Then" means? What does the (Row, 3) signify? I have had success with a very similar set of code for a excel spreadsheet. Is it the .txt that is the problem? Or could it have something to do with the field info Array instructions. They were there and I didn't think it would be a problem because the file comes in OK from the OpenDataFiles routine.

thanks for any help or insight you can offer

Walt



Sub OpenDataFiles()
Dim Datafile As Variant

Datafile = Application.GetOpenFilename(Title:="Please find and open the Towers file now")
If Datafile = False Then
MsgBox "Please provide the location of the datafile"
Datafile = Application.GetOpenFilename(Title:="Please find and open the Towers file now")
If Datafile = False Then
Exit Sub
End If
End If
' Opens the data file and imports just the relevant columns
Workbooks.OpenText FileName:=Datafile, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(10, 9), Array(22, 1), Array(52, 9), Array(63, 1), Array(67, 9), Array(68, 1), _
Array(74, 9), Array(75, 1), Array(80, 9), Array(81, 1), Array(87, 9), Array(105, 1), _
Array(135, 1), Array(161, 1))
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.Move Before:=ThisWorkbook.Sheets(2)

RemoveGarbageTowers
End Sub

Sub RemoveGarbageTowers()
Dim Row As Long
Dim Bot As Long
Bot = GetBottomRowOfColumn(Sheetname, 3)

With ActiveSheet
For Row = Bot To 1 Step -1
If .Cells(Row, 3).Value = "" Then
.Rows(Row).Delete
ElseIf .Cells(Row, 3) <> "AFF" Then
.Rows(Row).Delete
Row = Row + 1
End If
Next
End With
End Sub

 

[Egress1]

Howdy Walt,

"Bot = GetBottomRowOfColumn(Sheetname, 3)"

Looks like this line above calls a function named "GetBottomRowOfColumn)
The 2 arguments are the Sheetname (Sheet your working with) and the 3 I would assume is probably the column # this case being column C

Not familiar with this function is it an ADDIN function or do you have this function delcared in your code? It assigns the number of the last row to the variable BOT which is used in the loop below that counts down from the last row to the top row.

"For Row = Bot To 1 Step -1"

Now this loop will not work unless you have that function above declared ... if not BOT would be empty "" and the loop will not work.

"If .Cells(Row, 3).Value = "" Then"

Row is a variable that should equal the last row number the first time through this loop and count down by 1 until it reaches the top row or value of 1. Again the 3 references the third column in the active worksheet


Hope that helps

 

[walt1122]

Hi Ken, thanks for getting back to me. Yes it does look like it is delared here?
Function GetBottomRowOfColumn(Sheetname As String, ColumnNumber As Integer) As Long
' Gets the bottom row of a specified column

Dim Row As Long

With Sheets(Sheetname)
Row = .UsedRange.Rows.Count + .UsedRange.Row
While Row > 1 And Trim(.Cells(Row, ColumnNumber).Text) = ""
Row = Row - 1
Wend
End With
GetBottomRowOfColumn = Row
End Function

Thanks for trying to explain the (row, 3) thing. That is what I thought the 3 referenced, the third column. But still can not figure out why it doesn't work. I'm just looking for some code that will get rid of rows I don't want and keep the rows I do want based on some data in a row. In this case it is the "AFF" I want to sort against. I put the "AFF" in every column out to column "L" but the code still clears every line. Not that I know very much about any of this. Most of the macros I do are just using the recorder to do what I need. Any suggestions on how to fix this code or maybe scratch it and start over? I just thought I could use it and save some time. The whole project I'm working on revolves around taking two files and comparing the data to find the differences so a renconcilation can be done. One file has a lot of extra information that I don't need so I just want to get rid of it first. I wanted to automate as much as possible.

thanks again

Walt