Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: UsedRange

In VBA-Excel, Use­dRange is very effec­tive prop­erty when it comes to deal with the excel file which con­tains data. Right from for­mat­ting options of excel sheet to copy past­ing the data, get­ting infor­ma­tions about rows or columns or clear­ing 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)

For­mat:

WorkBook.WorkSheet.Use­dRange

Dim main­work­Book As Workbook

Set main­work­Book = ActiveWorkbook

Get the num­ber of Used rows

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

This will give you the num­ber 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 con­tents from the Excel Sheet

mainworkBook.Sheets(1).UsedRange.ClearContents

Clear Bor­der lines from the Excel Sheet

Use­dRange.ClearOut­line

Wrap Text in Excel Sheet

mainworkbook.Sheets(“DummySheet”).UsedRange.WrapText = True

 

You may also like...

1 Response

  1. Ron C says:

    Sug­gest adding Use­dRange com­mand for an entire work­book as a final cleanup.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: