Reset UsedRange without activating the sheet
I am looking at code that iterates over a large number of sheets and updates the data connections in each one. Since tables can change size significantly when updated, it is necessary to reset the used range on each sheet and the current code in place:
sh.Activate
ActiveSheet.UsedRange
Sheets(OriginalSheet).Activate
Simple question - I know just launching sh.UsedRange
doesn't work, but is there any other way to reset the range in use without activating the sheet?
source to share
VBA-Excel: clear data from Excel sheet (WorkSheet)
Format: WorkBook.WorkSheet.Range.Clear
Examples:
mainworkBook.Sheets(3).UsedRange.Clear
Clear data from a specific cell:
Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook
mainworkBook.Sheets("MyFirstMacro").Range("A2").Clear
'Will clear the data from Cell "A2"
Clear data from multiple cells:
mainworkBook.Sheets("MyFirstMacro").Range("A1,C3").Clear
'Will clear the data from Cell "A1" and "C3"
Clear data from a range of cells:
mainworkBook.Sheets("MyFirstMacro").Range("A1:C4").Clear
'Will clear the data from Range of Cells "A1" to "C4"
Clear data from a specific column:
mainworkBook.Sheets("MyFirstMacro").Range("A:A").Clear
'Will clear the data from entire column "A"
Remove data from a specific row:
mainworkBook.Sheets("MyFirstMacro").Range("2:2″).Clear
'Will clear the data from entire Row 2.
Remove all used data ranges:
mainworkBook.Sheets(3).UsedRange.Clear
Remove all data from more than columns
mainworkBook.Sheets("MyFirstMacro").Range("A:F").Clear
'Clear data for Columns from A to F
Remove all data from more than rows
mainworkBook.Sheets("MyFirstMacro").Range("2:5″).Clear
Credit: http://excel-macro.tutorialhorizon.com/vba-excel-clear-data-from-excel-sheetworksheet/
source to share