PDA

View Full Version : [SOLVED] selecting cells



lior03
06-14-2005, 06:10 AM
hello
suppose i select a range, A1 to G1 .now i want to select only odd cells in that selection namely the first the third or the fifth cell in that selection and clear there content.
is there a vba solution to this.
thanks
moshe

Norie
06-14-2005, 06:20 AM
Maybe something like this.


Set rng = Selection

For I=1 to rng.Count Step 2
rng.Cells(1,I).ClearContents
Next I

austenr
06-14-2005, 06:29 AM
That would work unless if there was data outside the specified range on that row you did not want to delete.

Norie
06-14-2005, 07:31 AM
That would work unless if there was data outside the specified range on that row you did not want to delete.
What do you mean?

Bob Phillips
06-14-2005, 08:04 AM
What do you mean?

What he means, I think, is that if selection spans 2 rows, you will merrily carry on clearing cells on the first row outside of the selected columns.

You should use


Set rng = Selection
For i = 1 To rng.Count Step 2
rng(i).ClearContents
Next i

which doesn't depend on the data being just 1 row, or


For Each cell In Selection
If cell.Column Mod 2 = 1 Then
cell.ClearContents
End If
Next cell


which will also take the odd columns in every row, even if the selection has an odd number of columns.

sheeeng
06-14-2005, 08:41 AM
hello
suppose i select a range, A1 to G1 .now i want so select only odd cells in that selection namely the first the third or the fifth cell in that selection and clear there content.
is there a vba solution to this.
thanks
moshe



YES, there is. :hi: Just copy the below code and put it in your sheet 1 vba code. :thumb (I based on what you meant, only one row to check) If you have a few rows, use / modify xld (http://www.vbaexpress.com/forum/member.php?u=2139)'s reply code above.



Option Explicit

Sub SelectAG()
Dim rng As Range
Dim I As Integer
Me.Range("A1:G1").Select
Set rng = Selection
For I = 1 To rng.Count Step 2
rng.Cells(1, I).ClearContents
Next I
End Sub
Private Sub Worksheet_Activate()
SelectAG
End Sub



:beerchug:

Zack Barresse
06-14-2005, 09:39 AM
Fwiw, you (usually) don't need to select anything really. So the last bit of code has two lines you could combine into one ..


Me.Range("A1:G1").Select
Set rng = Selection

'.. becomes ..


Set rng = Range("A1:G1")

sheeeng
06-14-2005, 06:49 PM
Thanks, firefytr (http://www.vbaexpress.com/forum/member.php?u=11). I learn a new thing today. Appreciate your reply.

Wow, you are a REAL fire fighter. Brave work. It is hard to imagine you are facing situation as protrayed like Ladder 49 daily and still manage to contribute to his forum.

Bravo.

Zack Barresse
06-15-2005, 09:42 AM
Well, it's not daily like that. Plus we have a lot more wildland fires than structure. We are a very rural department. Plus we're right on a freeway, so we get a lot of MVA's (Motor Vehicle Accident's). But thank you. I feel like an ordinary Joe getting to live out a childs dream. :yes