I have a button in sheet3.On the button click event I'm calling a macro.In the macro I want to select the number of cells that are filled in sheet13.How do I do this
Add a comment |
2 Answers
You cannot select cells without changing the focus of your sheet.
Sheets("sheet13").Activate ActiveSheet.UsedRange.Select You can, however, apply changes or read data from another sheet without changing focus.
Sheets("sheet13").UsedRange.Font.Bold = True Msgbox Sheets("sheet13").UsedRange.Cells.Count 
3 Comments
gizgok
Please read the question again.I have a button on sheet3.So when I do active sheet it will select the range of sheet3 and not sheet 13 as I need.If you are gonna tell me that I should activate sheet13 and then do this, then that's not what I need coz this switches the focus from sheet3 to sheet13 which I don't want.
gizgok
is there a way to activate the sheet but not shift the focus to that sheet
variant
@gizgok - Per the second set of lines in the above answer, you can apply changes / read data from a sheet without activating it provided you the range or object you want to modify or read is preceded by Sheets("sheetname").
As Variant says You cannot select cells without changing the focus of your sheet.
But you can use SpecialCells to select the cells
Sub tester() Dim x1 As Range Dim x2 As Range Dim bigRange As Range Sheets("sheet2").Select 'the page you need Range("E9").Select ' any select will do Selection.SpecialCells(xlCellTypeFormulas, 23).Select 'select numbers, text, etc. Set x1 = Selection Range("E9").Select ' any select will do Selection.SpecialCells(xlCellTypeConstants, 23).Select 'select formulas Set x2 = Selection Set bigRange = Application.Union(x1, x2) 'join both ranges bigRange.Select Sheets("sheet1").Select 'return to the page with the button End Sub the help of SpecialCells have aditional info of what can be selected.
