Fill series in visible cells only
 

Can any one suggest a way to fill only the visible cells in a filtere
range with a numerical series. I'm dealing with a data base for tissu
culture where after filtering the rows the filtered group are assigne
new experimental numbers the simplest being 1 - 96 but this serie
needs to skip all the filtered rows.

I think I need a VBA macro but don't know the programming language.
guess a macro needs to loop through the cells assesing their propert
for visibility or not and incrementing their value as appropriate

Thanks Ros

--
Message posted from http://www.ExcelForum.com

Fill series in visible cells only
 

Select the range and run this:-

Sub DataSeries()

Dim Start As Long
Dim Incr As Long
Dim cel As Range
Dim rng As Range

Set rng = selection.SpecialCells(xlCellTypeVisible)

Start = InputBox("What is the start value of your number series")
Incr = InputBox("What is the incremental value you wish to use")

For Each cel In rng
cel.Value = Start
Start = Start + Incr
Next cel

End Sub


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"RossM " wrote in message
...
Can any one suggest a way to fill only the visible cells in a filtered
range with a numerical series. I'm dealing with a data base for tissue
culture where after filtering the rows the filtered group are assigned
new experimental numbers the simplest being 1 - 96 but this series
needs to skip all the filtered rows.

I think I need a VBA macro but don't know the programming language. I
guess a macro needs to loop through the cells assesing their property
for visibility or not and incrementing their value as appropriate

Thanks Ross


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Fill series in visible cells only
 

Thanks for the macro it works very well - very elegant

One question

Why does the macro know that cel is the first cell in rng and only on
cell this doesn't seem to be defined anywhere in the code?
Regards
Ros

--
Message posted from http://www.ExcelForum.com