Neonovaz Posted October 29, 2017 Share Posted October 29, 2017 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 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) Link to comment Share on other sites More sharing options...
Neonovaz Posted October 31, 2017 Author Share Posted October 31, 2017 Any suggestions? Link to comment Share on other sites More sharing options...
gruntydatsun Posted October 31, 2017 Share Posted October 31, 2017 Put a whole version of this up so I can just paste it in and run and I'll have a look. Link to comment Share on other sites More sharing options...
Neonovaz Posted October 31, 2017 Author Share Posted October 31, 2017 (edited) 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 October 31, 2017 by Neonovaz Link to comment Share on other sites More sharing options...
gruntydatsun Posted October 31, 2017 Share Posted October 31, 2017 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? Link to comment Share on other sites More sharing options...
Juvigy Posted October 31, 2017 Share Posted October 31, 2017 I would say to just insert another column and do a formula on it depending on what you want to achieve. Link to comment Share on other sites More sharing options...
Neonovaz Posted October 31, 2017 Author Share Posted October 31, 2017 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 Link to comment Share on other sites More sharing options...
Neonovaz Posted October 31, 2017 Author Share Posted October 31, 2017 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 Link to comment Share on other sites More sharing options...
Neonovaz Posted November 1, 2017 Author Share Posted November 1, 2017 Any ideas? anyone? Link to comment Share on other sites More sharing options...
Juvigy Posted November 3, 2017 Share Posted November 3, 2017 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 Link to comment Share on other sites More sharing options...
Neonovaz Posted November 3, 2017 Author Share Posted November 3, 2017 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. Link to comment Share on other sites More sharing options...
Juvigy Posted November 6, 2017 Share Posted November 6, 2017 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. Link to comment Share on other sites More sharing options...
Neonovaz Posted November 6, 2017 Author Share Posted November 6, 2017 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now