How do I delete everything below row X in VBA Excel

Question

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

User · Answer

This function will clear the sheet data starting from specified row and column    Sub ClearWKSData wksCur As Worksheet  iFirstRow As Integer  iFirstCol As Integer   Dim iUsedCols As Integer Dim iUsedRows As Integer  iUsedRows   wksCur UsedRange Row   wksCur UsedRange Rows Count - 1 iUsedCols   wksCur UsedRange Column   wksCur UsedRange Columns Count - 1  If iUsedRows  gt  iFirstRow And iUsedCols  gt  iFirstCol Then     wksCur Range wksCur Cells iFirstRow  iFirstCol   wksCur Cells iUsedRows  iUsedCols   Clear End If  End Sub

User · Answer

It sounds like something like the below will suit your needs   With Sheets  Sheet1        Rows  X  amp       amp   Rows Count  Delete End With   Where X is a variable that   the row number   415

User · Answer

Any Reference to  Row  should use  long  not  integer  else it will overflow if the spreadsheet has a lot of data

User · Answer

Another option is Sheet1 Rows x  amp       amp  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

[excel] How do I delete everything below row X in VBA/Excel?

Examples related to excel

Examples related to vba