PDA

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.

GTO
09-06-2010, 10:17 PM
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