Find first row with two commas and delete

[nirvehex]

Hi,

I've attached a sample file, in which I have strings of text in column A. My goal is to create a VBA code to find the last row with two or more consecutive commas, ",," and select that row back up to row 2 and delete those rows. I have highlighted the row in yellow to point out what the last row with this stipulation should be. Also, once the last row is found, there will be no more rows with two or more consecutive ",," after this row.

I imagine the code would have to select row 2 to last row and run some sort of find function to select any row that has two or more consecutive commas in it and then do an End(xlUp) to row 2 and delete. I just don't know exactly how to do it, especially the find part.

Any ideas? Sample file is attached.



Thank you.

[snb]

I think this is a csv file.
If you want to remove all records from file "G:\OF\example.csv" that contain consecutive commas:


Sub M_snb()
with createobject("scripting.filesystemobject")
.createtextfile("G:\OF\example.csv").write join(filter(split(.opentextfile("G:\OF\example.csv").readall,vbcrlf),",,",0),vbcrlf)
end with
End Sub

[nirvehex]

snb,

Thanks for the advice. This data is from a notepad file that I'm pasting into an excel file. No CSV involved. Any ideas without using CSV code?

Thanks!

[Paul_Hossler]

Maybe something like this




Option Explicit
Sub DeleteSomeRows()
Dim iRow As Long

With ActiveSheet
For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
If InStr(.Cells(iRow, 1), ",,") = 0 Then
Range(.Rows(2), .Rows(iRow - 1)).Delete
Exit For
End If
Next iRow
End With
End Sub

[snb]

What's the path & name of the 'notepad' file ?

[Paul_Hossler]

the file 'appears' to be a download / extract from a stock tracking system.

If it is (or can be) downloaded to your \Downloads folder as a .txt or .csv or some other common format, snb's approach of reading the file directly into your WS, throwing away the records you don't need would be smoother

[nirvehex]

Thanks snb and Paul. I was almost going to use your code up there Paul, but I think you guys are right about importing directly from my .txt file.

I've reattached a sample file. The first tab, "Test" is the raw data. The second tab, "Test Scrubbed" is created using your code above Paul and a text to columns code that I made with the macro recorder.

Is there a way to write this code by using the .txt file and not having to paste the data into Excel each time? I understand snb's code above removes the lines with commas in it, but I'm wondering if there's a way to have the VBA code paste in the data and scrub it to make it look like the data on "Test Scrubbed" each time.

So basically the macro would open up a prompt for me to select the .txt file. Once I select it, the VBA code would run and create an output like seen on the "Test Scrubbed" tab. The raw data looks like that on "Test" and it's always in a .txt file.


Any thoughts?

Thanks!

[Paul_Hossler]

Might require a little tweaking

I did have to use your raw data sheet to make a csv file



Option Explicit
Sub ReadFileAndParse()
Dim iCSVnum As Long, iLineOut As Long, iColumnOut As Long
Dim sFileName As String, sLine As String
Dim wsOut As Worksheet
Dim v As Variant


'ask user
If MsgBox("Do you want to read a file?", vbQuestion + vbYesNo + vbDefaultButton2, "Read a file") = vbNo Then Exit Sub


'get file name
sFileName = Application.GetOpenFilename("My Filetypes, *.txt;*.csv;*.tsv")
If sFileName = "False" Then Exit Sub
Application.ScreenUpdating = False
'destination
Set wsOut = ActiveSheet
iLineOut = 1

'read until end
iCSVnum = FreeFile
Open sFileName For Input As #iCSVnum

Do While Not EOF(iCSVnum)
Input #iCSVnum, sLine

If InStr(sLine, ",,") = 0 Then
v = Split(sLine, ",")

For iColumnOut = LBound(v) To UBound(v)
wsOut.Cells(iLineOut, iColumnOut + 1).Value = v(iColumnOut)
Next iColumnOut

iLineOut = iLineOut + 1

End If
Loop
Close #iCSVnum
Application.ScreenUpdating = True


Call MsgBox("All Done!!", vbInformation + vbOKOnly, "Read a file")

End Sub

[nirvehex]

Paul,

My apologies. I should have attached the text file that I have to work with. I tested your code. It provides the exact output I'm looking for with the CSV file that you attached, but not the .txt file that I have. I've attached your file when I run it with the .txt file I have: 15813

I'd attach the .txt file but the forum won't let me.

Anyway, it looks exactly like your .csv file but it's a .txt file and opens with notepad. Any ideas how to have the code do the same thing with the .txt file?

Thanks!

[Paul_Hossler]

It'd really be a lot easier with the txt file as downloaded

To fool the uploader, just rename the file in Explorer as MyFile.txt.xlsx and see it that works

[snb]

You can use 'Hossler 001.csv' or 'Hossler 001.txt'; the extension doesn't matter.


Sub M_snb()
sn = Filter(Split(CreateObject("scripting.filesystemobject").opentextfile("G:\OF\Hossler 001.csv").readall, vbCrLf), ",,", 0)

Sheet2.Cells(1, 12).Resize(UBound(sn)) = Application.Transpose(sn)
sheet2.Columns(12).TextToColumns , , , , 0, 0, -1, 0, 0
End Sub

[nirvehex]

Paul,

I tried renaming it but it still won't work. I uploaded it to an old website of mine: http://caseitup.com/wp-content/uploads/2016/04/MyFile.txt.xlsx.txt

When you go to that link, if you copy and paste the exact text into a .txt file and open it with notepad that is the format I'm working with.

Thanks.

[nirvehex]

snb,

How do I use your code to open up a prompt to select the file rather than use the file path in the code?

Thanks.

[snb]

You can insert the method Paul suggested into the code I provided.
Don't feel too shy to do some coding yourself.

[Paul_Hossler]

Seems like a one line change




Line Input #iCSVnum, sLine



Not really a zip file, so just remove the .zip part of the name

[nirvehex]

Paul that's brilliant! That works perfectly...Thank you so much! snb thank you for your input as well. I'm still sharpening my skills in VBA.

Hey if any of you have a chance maybe you could take a look at a more complex coding problem I'm trying to figure out. It's posted here: http://www.vbaexpress.com/forum/showthread.php?55328

Anyway. Thanks again!