Used Range

Explain USED RANGE Method

This is useful to find used range (row or column) at sheet level

Last row\column is determined based on entire cells used in worksheet

Default row count is 1
Default column count is 1

Write a Program to find USED RANGE in a sheet

USED RANGE First row number in a sheet

First row number which consists of data in placed in range "B1"

USED RANGE Examples

Find Total Rows Count which consists of data
Find First Column Number
Find Total Columns Count
Find Total Cells in Used Range

USED RANGE - Columns count through variable

By defining variable we found columns which consits of data
Result mentioned in Range B1
Hidden columns also counted
Default value is 1, i.e in case Blank worksheet it returns 1

Last column number through defining variable

First column number through defining variable - Used Range

USED RANGE - Select Boarders

Private Sub CommandButton1_Click()
With ActiveSheet.UsedRange
r = Union(.Columns(1), .Rows(1), .Rows(2)).Select
End With
With Selection
.Interior.ColorIndex = 32
.Borders.Weight = xlThick
.Borders.LineStyle = xlDouble
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
ActiveSheet.UsedRange.Select
Selection.Offset(2, 1).Resize(Selection.Rows.Count - 3, Selection.Columns.Count - 1).Select
With Selection
.Interior.ColorIndex = 28
End With
End Sub
Private Sub CommandButton2_Click()
UsedRange.ClearFormats
End Sub

USED RANGE - First Row

Sub Firstrow_in_Used_Range()
MsgBox ActiveSheet.UsedRange.Row
Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column).Select
End Sub

USED RANGE Vs Current Region