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.
- Batch delete any line after a specified line in a specified sheet.
- Arbitrary lines can be specified manually each time the program is executed.
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
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.
I am Japanese, and my computer is set up in Japanese. So there may be some differences in the names of the buttons and windows.
I try to keep the information on this site (tamocolony) up-to-date, but please be aware that the information on this site may not be the most up-to-date, or the information itself may be incorrect. We take no responsibility for the content of this site. If you have any questions about an article or need to make corrections, please contact us via the Contact Us page.