Solved: Do not paste information on hidden row

[NY2DR]

Hello all,

I have a sheet that is filtered.
Several rows that do not fit my criteria are hidden.

Here is what I would like to have done:
1- I would like to copy information from one cell,
2- select a range on my filtered list,
3- then paste it
4- AND NOT have any part of the hidden rows affected.

Example:

Lets say I have a sheet with up to 100 rows with information.
After I filter it, every odd numbered row is hidden.
If ALL the even rows have the word "off" and I would like to change a certain range to "nuetral",(lets say rows A6:A62) I don't want my hidden rows to be changed to "nuetral"
I just want the even rows, which I selected, that are not hidden, to be changed to "nuetral"

Is this possible? I tried pastespecial and specialcells method with no luck.

Please advise.

Thank you,

[lucas]

Why not just do a simple find and replace on column A
[vba]Sub Replaceoff()
With Columns("A")
.Replace "off", "neutral", xlWhole
End With
End Sub[/vba]

[NY2DR]

I would do that but the problem is that I have to check each line individuly to make sure ths status is off.

And besides, I am using a filtered list, so I may be using a row that meets a certain date, whereas all the others may not.

Is there another solution?

[lucas]

select the cells you want in column A while they are filtered and use:
[VBA]Sub Replaceoff()
With Selection
.Replace "off", "neutral", xlWhole
End With
End Sub[/VBA]

[lucas]

I may have misunderstood you...Do you mean you are not filtering by off and on but by some other colulmn?

[lucas]

It still seems to work even if filtered by a different column.

[NY2DR]

Thanks Lucas,

it works very well.

I guess that I will have to change the "off" status to "on" in the macro everytime I need to change the status.

I'm curious, what if I have "ons" and "offs" in the same column, and I want to change it to nuetral? or any other status?

Any ideas?

If not, I'll be pleased with the above solution and keep on working with my VBA's.

[lucas]

multiple macro's that suit your need. You can do multiple replacements like this:
[VBA]Sub ReplaceMultiple()
With Selection
'replace 2101 with 2500...next line replace 2102 with 5500, etc.
.Replace "2101", "4500", xlWhole 'only need xlWhole on first replace
.Replace "2102", "5500"
.Replace "2103", "6300"
'etc
End With
End Sub[/VBA]

[NY2DR]

Thank You!

Very helpful!