Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hi Guys,
I found this code said to clear all contents of cells outside a dynamic range. How to modify to suit my need ? My dynamic range is
Range1 = Cells(1, 1).Resize(lastrowinRng, lastcolinRng).
I found this code said to clear all contents of cells outside a dynamic range. How to modify to suit my need ? My dynamic range is
Range1 = Cells(1, 1).Resize(lastrowinRng, lastcolinRng).
VBA Code:
Sub DeleteAllDataNotInSelection4()
Dim vArr As Variant, UnusedRow As Long, R1str As String, R1 As Range
Dim lastrowinRng As Long, lastcolinRng As Long
Dim Range1 As Range
lastrowinRng = Range("A" & Rows.Count).End(xlUp).Row
lastcolinRng = Cells(1, Columns.Count).End(xlToLeft).Column
Set Range1 = Cells(1, 1).Resize(lastrowinRng, lastcolinRng)
Debug.Print Range1.Address
Const DefinedName As String = Range1
Set R1 = Range(DefinedName)
R1str = "=" & R1.Parent.Name & "!" & R1.Address
UnusedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row + 1
Application.ScreenUpdating = Falseq
R1.Copy Cells(UnusedRow, "A")
Rows("1:" & UnusedRow - 1).Clear
Cells(UnusedRow, "A").Resize(R1.Rows.Count, R1.Columns.Count).Cut R1
ActiveSheet.Names.Add DefinedName, R1str
Application.ScreenUpdating = True
End Sub