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
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
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))))
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?
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))))}
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)
Hi
Another way to trap the error...
=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23);INDEX(A$3:A$2 3;SMALL(IF(LEN(A$3:A$23);ROW(A$3:A$23)-ROW(A$3)+1);ROWS(A$26:A26)));"")
again it's an array formula. confirmed with CTRL+SHIFT+ENTER
Formula will look like this:Quote:
Originally Posted by Admin
Code:=IF(ROWS(A$26:A26)<=COUNTA(A$3:A$23),INDEX(A$3:A$23,SMALL(IF(LEN(A$3:A$23),ROW(A$3:A$23)-ROW(A$3)+1),ROWS(A$26:A26))),"")
Thank you all
That's intentional. I guess the OP is using European version of Office.Quote:
Originally Posted by LalitPandey87