CtrlShftDown & a defined column to clear selection

Question

I need to select range "C6", then select all rows until a blank one using xlDown. I then need to combine that with column "Y" to then clear those contents.

For example if there are 25 rows of data as determined by xlDown starting at "C6", I need to create the range "C6:Y25" so I can use selection.ClearContents to delete the records.

Sub Delete_ALL()
Dim Warning As String
Dim lrow As Long

Warning = "Are you sure you want to delete your whole database of borrowers?"
Answer = MsgBox(Warning, vbQuestion + vbYesNo, "DELETE ALL???")
If Answer = vbNo Then
    Exit Sub
End If
Range("C6").Select
lrow = Range(Selection, Selection.End(xlDown)).Select
'Selection.ClearContents
End Sub

Unknown Unknown · Accepted Answer · 2018-12-04T03:39:42

Use column C to locate the xlDown then Offset to column Y.

dim rng as range

'column Y is 22 columns to the right of column C
set rng = range(cells(6, "C"), cells(6, "C").end(xldown).offset(0, 22))

debug.print rng.address(0,0)

You might want to error control whether xlDown will take you to the bottom of the worksheet.

dim rng as range

if not isempty(cells(7, "C")) then 
  set rng = range(cells(6, "C"), cells(6, "C").end(xldown).offset(0, 22))
else
  set rng = range(cells(6, "C"), cells(6, "Y"))
end if

debug.print rng.address(0,0)

CtrlShftDown & a defined column to clear selection

1 Answers