Delete entire row for all rows with data in Excel .csv file

[Asher]

Hello,

I am trying to make a button on a form in an Excel file that will open a .csv file that has had data dumped to it, figure out how many rows have data and delete all the rows, not just clear the contents, delete the entire rows, then close the file. (related to post:Excel VBA form that will save data in a csv file-reusable).

Something like:

Open .csv file
some way of finding the number of rows with data
delete all rows with data
save/close file

Any suggestions?

[HansV]

Why would you need to know how many rows with data there are? Just use Cells.Delete to remove everything.

[Asher]

Because I have this horrible tendency to over complicate... thanks for that tip. So here is my try at writing this code to do this:

Code: Select all

Private Sub cmdDeleteUploadedRecords_Click()

'Delete all data from

Dim csvWkbk As String
csvWkbk = "C:\Documents and Settings\mydrive\Desktop\LogFiles\TestLog.csv"

Workbooks.Open Filename:=csvWkbk
With Workbooks("TestLog.csv")
    .Sheets("TestLog").Cells.Delete
    .Save
    .Close
End With

End Sub

The only problem is, it still pops up a "Do you want to save" box and I already put the code in there to save. I am worried about this because if I leave it in and someone hits the "Cancel" button, it leaves the .csv file open.

Is there any way to get that to not pop up?

Also, I was thinking I should put a message box in the beginning of the code that asks if they really want to delete all the data. I haven't worked much with them so I am not sure how to say, if they hit yes, run the code, but if they hit no or cancel or any other button, stop the sub...?

[HansV]

Try this modification:

Code: Select all

Private Sub cmdDeleteUploadedRecords_Click()
  'Delete all data from
  Dim csvWkbk As String
  csvWkbk = "C:\Documents and Settings\mydrive\Desktop\LogFiles\TestLog.csv"

  If MsgBox("You are about to delete all data from " & csvwkbk & VbCrLf & _
      "Are you sure?", vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
  End If

  With Workbooks.Open(Filename:=csvWkbk)
    .Sheets("TestLog").Cells.Delete
    .Close SaveChanges:=True
  End With
End Sub

[Don Wells]

Hello,

I am trying to make a button on a form in an Excel file that will open a .csv file that has had data dumped to it, figure out how many rows have data and delete all the rows, not just clear the contents, delete the entire rows, then close the file. (related to post:Excel VBA form that will save data in a csv file-reusable).

Something like:

Open .csv file
some way of finding the number of rows with data
delete all rows with data
save/close file

Any suggestions?

I take it that you want to do something with the data in the csv file; otherwise you could just:

Code: Select all

Kill <pathname>

[Asher]

Thanks Hans, that modification works great! :-)

Thanks for your added information Don. But you are correct, the .csv file needs to be reusable and I think the kill command deletes a file doesn't it?

[HansV]

Yes, Kill deletes the file from disk.