Friday, 11 July 2014

How to Select Used Range Excluding Headers in Excel using VBA Macro

Excel VBA Macro to Select Used Range Excluding Headers
Sub Used_Rows_Columns_Range()
Dim RC As Long
Dim CC As Long
Dim Sort_Range As String

'To Get  Count of Columns and Rows with data in Used Range(Eg: 1,2,3)
RC = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

CC = ActiveSheet.Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

' To Get Column Index Name in Used Range(Eg: A,B,C..)

CN = Split(Cells(, CC).Address, "$")(1)

Data_Range = "$A$2" & " : " & "$" & CN & "$" & RC

ActiveSheet.Range(Data_Range).Select


End Sub

Example :



Other References for Dynamic Used Range :

How to Select Dynamic Actual Used Range in Excel


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts