HI
I want this table to be like this
HI
I want this table to be like this
Mahmoud, the sheet is in filter mode. You cannot expect to have the macro do what you what with the filter mode active. If you remove the filter, you can achieve what you want to do with a simple line of code
Code:Range("A3:E23").SpecialCells(xlCellTypeBlanks).Delete xlUp
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
I need a formula
Just use this A26=IFERROR(INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW (INDIRECT("1:"&ROWS(A$3:A$23))),""),ROW(A1))),"") as an array formula, and drag down and across
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
If the user office 2003
=IF(ISERROR(INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW (INDIRECT("1:"&ROWS(A$3:A$23))),""),ROW(A1)))),"",INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW (INDIRECT("1:"&ROWS(A$3:A$23))),""),ROW(A1))))
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
I'm sorry
FORMULA GIVEN EMPTY CELL
Please avoid statements like "Thank you very much/You are great/etc". If you like a post, just click on the Like button on the post. You can also like the Facebook button on the top of each thread, and post it to your facebook account.
You mention the formula is giving empty cell. Did you confirm the formula as an array formula?
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
YES
YOU MEAN LIKE THIS
{=IF(ISERROR(INDEX(A$3:A$23;SMALL(IF(LEN(A$3:A$23) ;ROW (INDIRECT("1:"&ROWS(A$3:A$23)));"");ROW(A2))));""; INDEX(A$3:A$23;SMALL(IF(LEN(A$3:A$23);ROW (INDIRECT("1:"&ROWS(A$3:A$23)));"");ROW(A2))))}
Last edited by mahmoud-lee; 06-09-2013 at 09:41 PM.
Post the workbook where you've used this array formula. By the way, why does your formula start with Row(A2), when I used Row(A1)
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Posting Permissions
Reply With Quote
Bookmarks