0

Hi there I have 200K rows in my excel project I need to delete about 30K of them. How can I do that without CTRL+Shifting for 30+mins. I’m unsure what else I can do. Please help

3
  • 1
    Hi Lucas and welcome! And how do these 30 thousand rows differ from the rest 170 thousand?
    – JohnSUN
    Oct 7, 2022 at 20:11
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Oct 7, 2022 at 20:24
  • 1
    If you can describe the criteria for which rows need to be deleted, we can probably provide you with a formula that will do this in seconds. Can you succinctly describe what it is about a row that makes it a row you want to delete? Also, do you have excel O365?
    – Max R
    Oct 8, 2022 at 0:27

2 Answers 2

0

As you're mentioning CTRL+Shifting, I guess the rows are all beneath each other. You may select multiple fields by typing into the name field (below the selected field name is A1, it's on the lower ribbon to the left).

enter image description here

I guess the functionality is best explained with an example: To delete rows 159k-189k in a table with colums named from A to K, you would type A159000:K189000 into the name field and all specified cells will be selected. Then you delete the selected area either with DEL and all values are gone (the empty cells will be still there), or you use the right click menu, click delete there and choose "Shift cells up" to delete the cells alltogether.

0

Make a macro - https://support.microsoft.com/en-us/office/quick-start-create-a-macro-741130ca-080d-49f5-9471-1e5fb3d581a8

From website: If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works.

As long as there is some sort of sequence to the records you are deleting this should work fine. Another alternative is to sort the records so all of the to-be-deleted ones are on top or bottom where you can easily click the last row, then scroll up or ctrl+f your way to the first to-be-deleted record (note when you select the first one, make sure you select the row. This way, you can hold shift and select the other row and delete as many rows as you want at once. It's all about getting Excel to order it for you, or to have the macro do the sequence thing for you. For example, a with a macro you can find a particular string, then delete that row, and repeat until done. Or you can record pushing the down button 3 times so it deletes every third record. You could even have it save automatically every 5th delete. Anything you can do can be recorded and saved as a macro.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.