Jump to content
Sign in to follow this  
Neonovaz

Autofill fitered excel rows/ find first row number in filtered sheet

Recommended Posts

Neonovaz

Hello

 

I'm using Excel UDF to filter data in an excel sheet. My aim is to autofill a column with a formula adjacent to the filtered column ((Eg: if filter is on column C, I want to apply autofill formula on column B)

 

I'm using _Excel_FilterSet to set the filter. However I'm having problem in figuring out how to use autofill here as the filtered range is mixed of random rows which meet the criteria

 

Workaround I have tried

I tried using  range areas to get the filtered data (as per Excel Wiki), but It doesn't tell me the row number of the first filtered row

 

any advise on how to go about this would be most helpful. Please let me know if some info is needed. My code snippet is below

 _Excel_FilterSet($oWorkbook6,"NOBYTNBR", Default , 3, "<>", 1) ;

 $oWorkbook6.Worksheets("NOBYTNBR").Range("C:C").Copy

global $oRange = $oWorkbook6.Worksheets("NOBYTNBR").Usedrange.SpecialCells($xlCellTypeVisible)
Local $aResult[1][$oRange.columns.Count], $aContent
; Read the data of all Ranges in the Area and concatenate the returned arrays.
For $oArea In $oRange.Areas
    $aContent = _Excel_RangeRead($oWorkbook6, "NOBYTNBR", $oArea, Default, True)
 _ArrayConcatenate($aResult, $aContent)
Next
_ArrayDisplay($aResult)

Share this post


Link to post
Share on other sites
gruntydatsun

Put a whole version of this up so I can just paste it in and run and I'll have a look.   

Share this post


Link to post
Share on other sites
Neonovaz
12 minutes ago, gruntydatsun said:

Put a whole version of this up so I can just paste it in and run and I'll have a look.   

Hello,

Thanks for your response

Well to run the whole thing you would require an excel which has some confidential data :(

But what I'm looking for is simple. An excel sheet with columns B and C , Where B has some xyz values in rows 1-20 while Column C has about 10 Values in random rows. The rest are blank in Column C.

Now we need to filter all except blanks on Column C (which i have achieved already) and then Copy contents of filtered rows of Column B to Column C (using maybe =B2) and then autofilling the rest of filtered rows in Column C.

this is the part I'm stuck at.  (tried using Ranged Areas as suggested by Water )  But I can only get the filtered data via it (can't use it to change anything in the actual excel filtered rows)

Edited by Neonovaz

Share this post


Link to post
Share on other sites
gruntydatsun

so get everything thats not blank in colC and append it to the end of colB?

so if i have

COLB   COLC
 1   |  A
 2   | 
 3   |  B
 4   |  C

What do you expect to see when it's done?

Share this post


Link to post
Share on other sites
Juvigy

I would say to just insert another column and do a formula on it depending on what you want to achieve.

 

Share this post


Link to post
Share on other sites
Neonovaz
20 minutes ago, gruntydatsun said:

so get everything thats not blank in colC and append it to the end of colB?

so if i have

COLB   COLC
 1   |  A
 2   | 
 3   |  B
 4   |  C

What do you expect to see when it's done?

 This is what I want as end result

COLB   COLC
 1   |  A
 2   | 
 3   |  B
 4   |  C

On apply filter for All except blanks

COLB   COLC
 1   |  A
 3   |  B
 4   |  C
 
 Final Result

COLB   COLC
 A   |  A
 B   |  B
 C   |  C

Result After Removing Filter

COLB   COLC
 A   |  A
 2   |
 B   |  B
 C   |  C

Share this post


Link to post
Share on other sites
Neonovaz
24 minutes ago, Juvigy said:

I would say to just insert another column and do a formula on it depending on what you want to achieve.

 

Hello

Thanks for your response

 

Well the problem with that is I cannot know which row to apply the formula at... (as stated in the topic header)

If somehow I can get the 2nd row number (in the filtered column)  I can put the formula there and do autofill maybe

But problem is how to get that 2nd row number since I have no way of knowing what rows would be filtered out

Share this post


Link to post
Share on other sites
Juvigy

1. Insert a new column or use and existing one that is empty - lets call it 'COLD'

2. Put a formula = IF(C2 = "",B2,C2)

3 Paste values from column D to column B

 

Share this post


Link to post
Share on other sites
Neonovaz
59 minutes ago, Juvigy said:

1. Insert a new column or use and existing one that is empty - lets call it 'COLD'

2. Put a formula = IF(C2 = "",B2,C2)

3 Paste values from column D to column B

 

Hi

Thanks for your response

 

Well The problem with this method is... how do I tell the script where to insert the formula? There is no way to figure out what is the first filtered row number (even for a new column).

 

If somehow we can get the row number of the first/second row that comes up after using filter, then yes i think this might work.

 

 

Share this post


Link to post
Share on other sites
Juvigy

You dont need to filter anything. Just do:

$oExcel.Application.ActiveSheet.UsedRange.Select
$oExcel.Application.Selection.Address

This will give you something like $B$1:$C$4 which will help you determine where to put the formula. Then you can insert the formula in D1:D4 and you will have the desired effect.

Share this post


Link to post
Share on other sites
Neonovaz
6 hours ago, Juvigy said:

You dont need to filter anything. Just do:

$oExcel.Application.ActiveSheet.UsedRange.Select
$oExcel.Application.Selection.Address

This will give you something like $B$1:$C$4 which will help you determine where to put the formula. Then you can insert the formula in D1:D4 and you will have the desired effect.

Thanks ! I will try that

However for now i did a workaround by sorting the filled cells to the top, counting them and then adding the formula on the other column using the count from the first :)

they start from A2 .. so problem fixed!

Thanks everyone for your support and response

Topic Closed

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.