How to delete all rows except the first header row in Excel?
When working with data in Excel, you may need to remove all rows except the first row (header row) while keeping the structure intact. This is useful when you want to clear old data while retaining column headers. In this guide, we'll explore three methods to achieve this efficiently:
Delete all rows except the first header row with shortcuts
Delete all rows except the first header row with Kutools AI
Delete all rows except the first header row with VBA code
Delete all rows except the first header row with shortcuts
If you have a small dataset and want a quick way to delete all rows except the first row manually.
- Click on row number 2 (the first row below the header).
- Press "Ctrl + Shift + ā" to select all rows below the header row.
- Then, press "Ctrl + -" keys, the selected range is deleted at once, only keep the header row.
Delete all rows except the first header row with Kutools AI
For a faster and fully automated solution, Kutools for Excel provides a powerful tool. With "Kutools AI Aide", you can simply describe your task in natural language, and it will instantly process the data for you. This makes it an ideal one-click solution for handling large datasets efficiently.
After installing Kutools for Excel, please click "Kutools" > "AI Aide" to open the "Kutools AI Aide" pane. In the pane, please specify the following steps:
- Select the data range that you want to use.
- Then, type the requirement in the chat box: such as:
Delete all rows except the first header row in this worksheet - And then, press Enter key or click the "Send" button. Kutools AI will analyze the question, after finishing, please click "Execute" button to get the result.
Delete all rows except the first header row with VBA code
For advanced users, VBA provides a highly customizable and automated way to delete rows. This method is ideal for repetitive tasks or complex data transformations.
1. Press "Alt + F11" keys simultaneously to open the "Microsoft Visual Basic for Applications" window.
2. In the Microsoft Visual Basic for Applications window, click "Insert" > "Module". Then copy and paste the following VBA code into the Code window.
VBA code: Delete all rows except the first header row in Excel
Sub DeleteExceptFirst()
Rows("2:" & Rows.Count).ClearContents
End Sub3. Press the F5 key to run the code, then all rows except the first header row are deleted from the active worksheet immediately.
Deleting all rows except the first header row in Excel can be achieved through various methods, each suited to different user needs and dataset sizes. For quick and simple tasks, keyboard shortcuts may suffice. For maximum ease and automation, Kutools AI is an excellent choice. Finally, for advanced customization and automation, VBA code provides unparalleled flexibility.
By understanding these methods, you can choose the most efficient approach for your specific scenario, saving time and improving your productivity in Excel. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Related articles:
- How to delete all rows below certain row or active cell in Excel?
- How to delete rows if cell is greater/less than a certain value in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in