Select all cells below a continuous range to delete

[cyburban]

Hello,

I am new to the VBA game but am trying to automate a repetitive task at work. I have some coding experience but no VBA experience, so I have been using the macro recording function. I am struggling with how to delete excess rows below my continuous data set. I am working with data sets that have a defined width but variable height. I know the maximum number of rows will be less than 350 so I have set my formulas processing the data to end there. What I am trying to accomplish is clearing all the excess rows below my continuous data in column C. The formatting is somewhat attrocious as a bot is processing the spreadsheet after the data is moved to the correct format. A human needs to take a pass to cleanup anything violating rules within the bot hence the conditional formatting. Basically, I know column C end is the end of actual data. You can see in G, W,X,Y there is data extending below what is in column C. I know this excess data stops in column DN. How can I determine the row of the start of this excess data to use cntrl+shift+down to select the rest to delete in a general way?

Below is my first attempt via macro recording. I selected the top left cell in column c, cntrl+shift+down, c+. to select the bottom cell. Then down + right 4 times to get into the correct position.

Sub DeleteExtra()
'
' DeleteExtra Macro
'

'
Sheets("CalSHAPE").Select
ActiveWindow.SmallScroll Down:=-44
Range("C7").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C59").Select
Range("G59").Select
Range("G59:DN59").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub

This has rigidly defined cells in it. Is there any way to make it generalized so it just begins form the bottom of the data?

First post here. Please let me know if you need any additional information or clarification.

 

[StephenCrump]

Welcome to the Forum!

Will this work for you?

'Deletes all rows In "Sheet1" below the last populated row in Column C
With Worksheets("Sheet1")
    .Rows(.Range("C" & Rows.Count).End(xlUp).Row + 1 & ":" & Rows.Count).EntireRow.Delete
End With

 

[cyburban]

Welcome to the Forum!

Will this work for you?

'Deletes all rows In "Sheet1" below the last populated row in Column C
With Worksheets("Sheet1")
    .Rows(.Range("C" & Rows.Count).End(xlUp).Row + 1 & ":" & Rows.Count).EntireRow.Delete
End With

Works perfectly thank you!