Be the first user to complete this post

  • 0
Add to List

VBA-Excel: UsedRange

In VBA-Excel, UsedRange is very effective property when it comes to deal with the excel file which contains data. Right from formatting options of excel sheet to copy pasting the data, getting informations about rows or columns or clearing the data from excel.

As the name clearly states that it talks about all the cells in an Excel which are filled. ( All the Range which is used)

Format:

WorkBook.WorkSheet.UsedRange

Dim mainworkBook As Workbook

Set mainworkBook = ActiveWorkbook

Get the number of Used rows

mainworkBook.Sheets(“Sheet1”).UsedRange.Rows.Count

This will give you the number of Rows which are used.

Clear all the data from the Excel Sheet

mainworkBook.Sheets(3).UsedRange.Clear

Copy the entire data from the Excel Sheet

mainworkBook.Sheets(3).UsedRange.Copy

To know more about copy/paste the data using VBA-Excel click here.

Clear all the contents from the Excel Sheet

mainworkBook.Sheets(1).UsedRange.ClearContents

Clear Border lines from the Excel Sheet

UsedRange.ClearOutline

Wrap Text in Excel Sheet

mainworkbook.Sheets("DummySheet").UsedRange.WrapText = True



Also Read:

  1. Message Boxes in VBA Excel (Msgbox)
  2. VBA-Excel: Create a WorkBook at Runtime.
  3. VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order
  4. How to Enable Macro in Excel
  5. Excel-VBA : Insert Multiple Images from a Folder to Excel Cells
  6. Excel-VBA : Open a Excel File using Another Excel File using Browse Option.
  7. Excel-VBA : Change Passwords for all the WorkSheets in one shot
  8. VBA-Excel: Delete Blank Rows from Excel Work Sheet