Menu
I have a long variable X that contains a number. Say it's 415.
How do I delete everything on the worksheet from Row 415 and below?
I want to ensure my spreadsheet is clean in Row 415 and anything else that might be below it.
How do I do this? Thanks.
634
In VBA, there is a method called ClearContents that you can use to clear values and formulas from a cell, range of cells, and the entire worksheet. To use this method, first, you need to define the expression somewhere you want to clear the content, and then type “. ClearContents”.
Method-1: Using Delete Sheet Rows Option You can do this by using the Delete Sheet Rows Option. Then, all of the cells of the last three rows will be selected. Result: In this way, all of the unwanted rows below a certain will be deleted.
Code: Range (“A1:C3”).Delete This will delete the mentioned cell values, just like our clear method has done. If you want to delete all the cell's data, then you can use VBA CELLS property. In VBA concepts, cells are also the same, no different from normal excel cells.
In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the following VBA code into the Code window. 3. Press the F5 key to run the code, then all rows except the first header row are deleted from the active worksheet immediately.
It sounds like something like the below will suit your needs:
With Sheets("Sheet1") .Rows( X & ":" & .Rows.Count).Delete End With Where X is a variable that = the row number ( 415 )
60
Another option is Sheet1.Rows(x & ":" & Sheet1.Rows.Count).ClearContents (or .Clear). The reason you might want to use this method instead of .Delete is because any cells with dependencies in the deleted range (e.g. formulas that refer to those cells, even if empty) will end up showing #REF. This method will preserve formula references to the cleared cells.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
