Home

My excel sheet keeps recreating rows after I delete them

%3CLINGO-SUB%20id%3D%22lingo-sub-481340%22%20slang%3D%22en-US%22%3EMy%20excel%20sheet%20keeps%20recreating%20rows%20after%20I%20delete%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481340%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20task%20that%20requires%20a%20separate%20workbooks.%20In%20my%20second%20workbook%20there%20is%20an%20extra%20100%2B%20rows%20that%20I%20keep%20deleting%20and%20they%20are%20reappearing.%20I%20have%20tried%20to%20choose%20CTRL%2BEnd%20and%20the%20pressed%20clear%20formatting%20all%26nbsp%3B%20for%20the%20rows%2C%20but%20then%20they%26nbsp%3B%20come%20back%20again.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20stop%20this%20from%20happening%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-481340%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481621%22%20slang%3D%22en-US%22%3ERe%3A%20My%20excel%20sheet%20keeps%20recreating%20rows%20after%20I%20delete%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20Ctrl%2BShift%2BEnd%20selects%20all%20rows%20till%20end%20of%20the%20worksheet%20(1M%20something)%2C%20not%20till%20Ctrl%2BEnd.%20If%20I%20understood%20correctly%20that's%20not%20empty%20rows%20between%20two%20ranges%2C%20these%20are%20not%20cleaned%20rows%20below%20the%20range%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481597%22%20slang%3D%22en-US%22%3ERe%3A%20My%20excel%20sheet%20keeps%20recreating%20rows%20after%20I%20delete%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481597%22%20slang%3D%22en-US%22%3ESelect%20the%20row%20immediately%20below%20your%20data%20and%20press%20Ctrl%2BShift%2BEnd%20to%20select%20all%20blank%20rows%20below%20your%20data.%20Press%20Ctrl%2B-%20and%20select%20Rows%2C%20then%20click%20Ok%20to%20delete%20all%20the%20selected%20rows.%20Save%20your%20file.%20If%20you%20now%20press%20Ctrl%2BEnd%2C%20you%20will%20jump%20to%20the%20rightmost%20cell%20in%20the%20last%20row%20of%20your%20data%2C%20which%20indicates%20that%20you%20successfully%20deleted%20all%20blank%20rows%20below%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481419%22%20slang%3D%22en-US%22%3ERe%3A%20My%20excel%20sheet%20keeps%20recreating%20rows%20after%20I%20delete%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481419%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325739%22%20target%3D%22_blank%22%3E%40monica91%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20I%20understand%20correctly%20you%20have%20empty%20rows%20all%20the%20time%20at%20the%20bottom%20of%20you%20range%3F%20To%20clean%2C%20select%20all%20these%20empty%20rows%20(from%20end%20of%20the%20range%20till%20the%20roe%20where%20Ctrl%2BEnd%20is%20positioned)%2C%20Home-%26gt%3BClear-Clear%20All.%20Save%20and%20reopen%20workbook.%20As%20a%20rule%20Ctrl%2BEnd%20shall%20be%20at%20the%20end%20of%20your%20range%20after%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481413%22%20slang%3D%22en-US%22%3ERe%3A%20My%20excel%20sheet%20keeps%20recreating%20rows%20after%20I%20delete%20them%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481413%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325739%22%20target%3D%22_blank%22%3E%40monica91%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E100%20rows%20of%20%E2%80%A6%20%2C%20what%20exactly%3F%3C%2FP%3E%3CP%3EThe%20sheet%20will%20always%20have%201048576%20rows%2C%20so%20any%20rows%20you%20delete%20will%20be%20replaced%20by%20blank%20rows.%3C%2FP%3E%3CP%3EThese%20will%20reflect%20any%20whole%20column%20formatting%20that%20might%20be%20in%20play.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I am working on a task that requires a separate workbooks. In my second workbook there is an extra 100+ rows that I keep deleting and they are reappearing. I have tried to choose CTRL+End and the pressed clear formatting all  for the rows, but then they  come back again. 

 

How can I stop this from happening? 

4 Replies
Highlighted

@monica91 

100 rows of … , what exactly?

The sheet will always have 1048576 rows, so any rows you delete will be replaced by blank rows.

These will reflect any whole column formatting that might be in play.

Highlighted

@monica91 ,

 

Do I understand correctly you have empty rows all the time at the bottom of you range? To clean, select all these empty rows (from end of the range till the roe where Ctrl+End is positioned), Home->Clear-Clear All. Save and reopen workbook. As a rule Ctrl+End shall be at the end of your range after that.

Highlighted
Select the row immediately below your data and press Ctrl+Shift+End to select all blank rows below your data. Press Ctrl+- and select Rows, then click Ok to delete all the selected rows. Save your file. If you now press Ctrl+End, you will jump to the rightmost cell in the last row of your data, which indicates that you successfully deleted all blank rows below it.
Highlighted

@Twifoo , Ctrl+Shift+End selects all rows till end of the worksheet (1M something), not till Ctrl+End. If I understood correctly that's not empty rows between two ranges, these are not cleaned rows below the range,

Related Conversations
Fixieren in Exel
nicoalex12 in Excel on
1 Replies
Combine/Rearrange Rows and Columns
Rob Nunley in Excel on
2 Replies
Excel: Condensing and Counting
MADavis in Excel on
2 Replies
VBA code to reset togglebutton colors.
dick_hollister in Excel on
0 Replies
Horizontal Scrolling
stephen607 in Excel on
0 Replies
How to in Excel
HarryNetherlands in Excel on
1 Replies