【VBA】How to delete all lines after a given line and output CSV for each sheet.

For tasks such as user registration, importing by CSV file is still the mainstream for most systems.

When creating the CSV file, I created a file for each sheet to be imported for multiple systems.

Each of these sheets had the same number of rows, so I created a VBA that could delete any row in a batch. The advantage of this VBA is that you can specify how many lines to delete each time you run it.

Excel sample

VBA code content

Sub lineDeletion()
　　ActiveWorkbook.Save
　　Dim ws As Worksheet
　　Dim ans As String

　　Sheets("Sheet1").Select
　　Range("A1").Select

　　ans = InputBox("数字のみ入力してください", "削除を開始したい行を入力", "")

　　Worksheets("Sheet1").Rows(ans & ":" & Worksheets("Sheet1").Rows.Count).Delete
　　Worksheets("Sheet2").Rows(ans & ":" & Worksheets("Sheet2").Rows.Count).Delete
　　Worksheets("Sheet3").Rows(ans & ":" & Worksheets("Sheet3").Rows.Count).Delete

　　For Each ws In Worksheets

　　ws.Activate
　　ActiveWorkbook.SaveAs _
　　Filename:=ActiveWorkbook.Path & "\" & ws.Name & "_" & Format(Date, "yyyymmdd") & ".csv", _
　　FileFormat:=xlCSV

　　Next ws

　　MsgBox "ファイル作成が完了しました。"
　　Application.Quit
　　ActiveWorkbook.Close SaveChanges:=True
End Sub

How to use VBA

This file will be converted to CSV at once and run until Excel is closed. The CSV file is located in the same hierarchy as the Excel file, so if you want to modify it for use, I recommend storing it in a dedicated folder.

The operation is very simple.

When you run the program, it first selects “A1” of “Sheet1” and displays a pop-up window.

▲The text displayed at this time can also be changed.”If you don’t want the fourth line or later,” you can enter “4” to make the CSV with the fourth line or later completely deleted from all the sheets you set.

If you want to change or increase the name of the target sheet, you can change the following part.

Worksheets("Sheet1").Rows(ans & ":" & Worksheets("Sheet1").Rows.Count).Delete
Worksheets("Sheet2").Rows(ans & ":" & Worksheets("Sheet2").Rows.Count).Delete
Worksheets("Sheet3").Rows(ans & ":" & Worksheets("Sheet3").Rows.Count).Delete

conclusion

In this article, I introduced a VBA that I created just for myself that deletes everything after a specific line.

I found a similar VBA on the net, but I like it better because I wanted to specify the number of rows to delete each time.