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?
Delete entire row for all rows with data in Excel .csv file
-
- 2StarLounger
- Posts: 169
- Joined: 08 Jun 2010, 14:33
- Location: Massachusetts, USA
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Delete entire row for all rows with data in Excel .csv f
Why would you need to know how many rows with data there are? Just use Cells.Delete to remove everything.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 169
- Joined: 08 Jun 2010, 14:33
- Location: Massachusetts, USA
Re: Delete entire row for all rows with data in Excel .csv f
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:
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...?
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
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...?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Delete entire row for all rows with data in Excel .csv f
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Delete entire row for all rows with data in Excel .csv f
I take it that you want to do something with the data in the csv file; otherwise you could just:Asher wrote: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?
Code: Select all
Kill <pathname>
Regards
Don
Don
-
- 2StarLounger
- Posts: 169
- Joined: 08 Jun 2010, 14:33
- Location: Massachusetts, USA
Re: Delete entire row for all rows with data in Excel .csv f
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?
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?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Delete entire row for all rows with data in Excel .csv f
Yes, Kill deletes the file from disk.
Best wishes,
Hans
Hans