Last Cell Selection Methods



VBA Code to Select All used Rows and Column in a sheet?
'ThisWorkbook.Sheets("Sheet1").UsedRange.Select
'ThisWorkbook.Sheets("Sheet1").Range("a1").CurrentRegion.Select

VBA Code to Select Last Used Cell of Row?
Selection.End(xlDown).Select
Range("A100000").End(xlUp).Select

VBA Code to Select Last Cell of Row?
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
‘or
Range(Range("A100000").end (xlup)).select
Range(Selection, Selection.End(xlup)).Select

VBA Code to Select Last Used Cell of Column?
Selection.End(xltoRight).Select
Range("XFD1").End(xltoleft).Select

VBA Code to Select Last Cell of Column?
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("XFD1").End(xlToLeft).Select
Range(Selection, Range("a1")).Select

Comments

Post a Comment

Popular posts from this blog

Copy diff workbook data in one

Sub Copy_different_file_data_in_one_file() 'VBA Macro Code to Copy Different workbook data in one workbook and compile it MkDir "D:\Study\Excel and VBA Practice\Macro18Jan" MkDir "D:\Study\Excel and VBA Practice\Macro18Jan\VBAClass" Workbooks.Add ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Macro18Jan\VBAClass\Jan.xlsx" Workbooks.Add ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Macro18Jan\VBAClass\Feb.xlsx" Workbooks.Add ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Macro18Jan\VBAClass\Mar.xlsx" Workbooks.Add ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Macro18Jan\VBAClass\Working.xlsx" Workbooks("Jan.xlsx").Sheets("Sheet1").Activate Range("A1").CurrentRegion.Copy Workbooks("working.xlsx").Sheets("Sheet1").Activate Range("A1").PasteSpecial xlPasteValues Application.CutCopyMode = False ActiveSheet.Name...
Read more

'Macro program for Sumif functions or Array

'Macro program for Sumif functions or Array Sub Macro2Sumif()     Range("B1").Select     Range(Selection, Selection.End(xlDown)).Select     Selection.Copy     Range("S1").Select     ActiveSheet.Paste     Application.CutCopyMode = False     Range("S1").Select     Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo     Range("T1") = "English"     Range("U1") = "Hindi"     Range("V1") = "Math"     Range("W1") = "Science"     Range("X1") = "Computer"     For R = 2 To 4 For C = 20 To 24 Cells(R, C) = Application.WorksheetFunction.SumIf(Range("$B$2:$G$201"), Cells(R, 10), Range("c$2:c$201")) / Application.WorksheetFunction.Sum(Range("c$2:c$201")) Next C Next R End Sub
Read more

Find lowest value

'Macro Program to find Lowest Value and Bold in Range A:F Sub Macroto_Bold_MinValue()     For J = 1 To Application.WorksheetFunction.CountA(Range("a:a"))         x = Application.WorksheetFunction.Min(Range("A" & J & ":f" & J))             For i = 1 To 6                 If (Cells(J, i) = x) Then                     Cells(J, i).Font.Bold = True                 Else                     Cells(J, i).Font.Bold = False                 End If             Next i     Next J End Sub
Read more