View Full Version : How to select a range of cells which are filled in not an active sheet.
szcukg
09-06-2010, 12:11 PM
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 sheet7.How do I do this
mikerickson
09-06-2010, 07:22 PM
Why do you want the cells selected? Most of the time it's not necessary for a macro to select cells.
szcukg
09-06-2010, 09:16 PM
I need to do operations on all filled cells of mulitple sheets.Hence as I go from sheet to sheet I need to select a rang eof cells
mikerickson
09-06-2010, 09:48 PM
One usualy doesn't need to do selection.
This will copy a range from Sheet1!A1:B3 to Sheet2!C4
Sheets("Sheet1").Range("A1:B3").Copy Destination:=Sheets("Sheet2").Range("C4")
IF you only are concerned about moving the values
Sheets("Sheet2").Range("C4:D6").Value = Sheets("Sheet1").Range("A1:B3").Copy.Value
What operations being performed on those cells in different sheets?
szcukg
09-06-2010, 09:57 PM
Will this display me the filled values of sheet2 range in sheet1.If it does that then i do not need that.I just need to iterate over a range of filled cells in a macro.
Could you show us a sample workbook and describe what is to be accomplished?
mohanvijay
09-07-2010, 02:36 AM
try this
Dim a As Range
Dim b As Range
Set a = Sheets(7).UsedRange
For Each b In a
If b.Value <> "" Then
m = m & b.Address & ","
End If
Next
m = Left(m, Len(m) - 1)
Sheets(7).Activate
Range(m).Select
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.