|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 22nd, 2002, 12:00 PM
|
#1 |
|
Board Regular
Join Date: May 2002
Location: CALIFORNIA
Posts: 137
|
Heres what i want to do
I have data in a range A2:A51, sometimes there will be blanks. I need to look up find the first cell with data and put it in B2,then B3 and so on. any ideas thanks steve ------A----------B 1---Data--------Filtered lookup 2---------------5 3---------------10 4---------------30 5------5---------2 6-----10---------7 7---------------30 8---------------50 9-----30---------2 10 11-----2 12-----7 13----30 14 15----50 16-----2 17 [ This Message was edited by: white6174 on 2002-05-22 11:10 ] [ This Message was edited by: white6174 on 2002-05-22 11:13 ] [ This Message was edited by: white6174 on 2002-05-22 11:13 ] |
|
|
|
May 22nd, 2002, 12:54 PM
|
#2 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
Hi,
Try the following: Code:
Sub test()
Dim Rng1 As Range, UsedCell As Range
With ActiveSheet
Set Rng1 = .Range("A2:A51")
For Each UsedCell In Rng1
If Len(UsedCell) Then
.Cells(.Cells(Rows.Count, "B").End(xlUp).Row + 1, 2) _
= UsedCell.Value
End If
Next UsedCell
End With
End Sub
Jay |
|
|
|
|
May 22nd, 2002, 01:04 PM
|
#3 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
As the figure below shows, you could apply Advanced Filter to your data range:
A2 houses the criterion: =A5<>"" Activate A2. Activate Data|Filter|Advanced Filter. Check Copy to another location. Make sure that the List range contains the range of interest: $A$4:$A$11 Enter $A$1:$A$2 for Criteria range. Enter e.g., $C$4 for Copy to. Leave Unique records only unchecked. Activate OK.
To see the formula in the cells just click on the cells hyperlink The above image was automatically generated by [HtmlMaker V1.27] If you want FREE SOFT, click here and Colo will email the file to you This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo Note. The original list can also be filtered to a different worksheet in the same workbook. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
May 22nd, 2002, 02:14 PM
|
#4 |
|
Board Regular
Join Date: May 2002
Location: CALIFORNIA
Posts: 137
|
Thanks aladin,
but I need this list to updata also like the last one you helped me with last time. thanks steve w |
|
|
|
|
May 22nd, 2002, 03:46 PM
|
#5 |
|
Board Regular
Join Date: May 2002
Location: CALIFORNIA
Posts: 137
|
I found an answer thanks for the help though
In case someone else needed the answer Data is the defined range to be filtered. (I got this code out of a book EXCEL 2000 POWER PROGRAMING) {=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))} |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
