I have an excel file that had 8000 rows with data this has been reduced now to 4000 rows however when I use the side bar to scroll down it stil "thinks" that the 8000th cell has data in it. deleting the cells themselves doesnt work either how do I make those cells truly empty? So that when i use the sidebar it wont go further than the last cell with data in it?
the first option only removes blanks it doesnt further delete/remove the rows themselves i cant get the script to run and im not going to install the program (im working on a pc from my work)
surely theres an option in excel wich does that?
Select one of the not-wanted blank cells in your table, and press ctrl+A,scroll all the way down to the bottom,move your mouse to the bottom border of the selection area,you will notice the mouse cursor has changed into a line with two arrows,now,left-click and hold it,move your mouse all the way up to the last row of your data(i.e. 4000th row,in your case),release the left-click,you get it cut into 4000 rows!
You'll also need to remove any formatting changes you've made to the bottom rows.
You mean just collaps those rows? Make their height zero? I already knew that but later on I might add more data then i have to uncolapse them... isnt there a normal ption to get this done?
If you want to delete rows totally you select the rows (one column of the row is enough) then you right click with your mouse somewhere over the selected rows and on the contextual menu that appears you left click on Delete... That opens a window with four options where you choose the option that says "Entire row" and you click OK. (amount other ways)
The scrolling means absolutelly nothing. If the print view shows just the rows with data then the spreadsheet is correctly updated.
If you don't like the scrolling just save the file, close and reopen and it should be updated too.
When coding in VB then after deleting the rows you have to use ActiveSheet.UsedRange