Search results

Hi RX, sample code is above. I have a spread sheet with some 20,000 rows and some 80 columns. I only want some data and so using code create a new spread sheet copying across only the columns I want. This still gives me rows of information I do not need. In one of the columns is an ID of...

I found this code at http://stackoverflow.com/questions/10849177/easiest-way-to-loop-through-a-filtered-list-with-vba Option Explicit Sub Sample() Dim rRange As Range, filRange As Range, Rng as Range 'Remove any filters ActiveSheet.AutoFilterMode = False '~~> Set your...

RX thank you for your reply. alas for some reason when I try to open your excel file which looks like an xls file I get cannot open a php file message. I looked at the first link but it's blown my mind I don't understand it. Do I need both the formula and code? I don't understand the...

Ranman256, counting how many times book appears in a single column is exactly what I want to do. Book will not appear in any other column. what is making it beyond me, is counting how many times it appears in that column via only the visible cells. smiler44

I want to know how many times something appears in a column in the visible cells after filtering. for example book appears 12 times in a column, 3 are visible and 9 are hidden. I need the VBA code to return 3. Below is what I have but it includes both hidden and visible instances dim...

thanks gents I will try these suggestions. did not think for one moment it was going to be this difficult smiler44

Thanks Trevor G I've had a read. My functions will be contained within what ever I distribute. you said there are there are several options. can you expand on that and tell me how? Thanks smiler44

I have written some macros that may be distributed to other people. It would be nice to have the macros run from the personal work book. Is there a way I can put macros and modules into their personal work book automatically by running a macro such as an "install" macro contained within the...

thanks for the replies I think this is solved smiler44

how reliable would this code be? this seemed to work and I'm surprised so I do ask if this would be reliable on a filtered column Sub Macro1() Dim lst As String Dim lr As String ActiveSheet.Range("a2").Select ' row to start If ActiveCell.Offset(1, 0) <> "" Then...

borebende, thanks for the reply. Recording a macro alas will always get the exact range I select but may range will vary. I need to work out the range. mmm let me go try something smiler44

For excel 2007 and 2010 I want to filter a spread sheet and copy the data that is visible. I don't know the range of the visible cells, how do I get the range of the visible cells? I think if I just copy visible cells, it copies all blank cells that are outside of my filtered range...

Brian my goodness no need to apologise your always very helpful. whenever I see your user name on one of my posts I know the issue will be resolved. smiler44

no worries. all sorted and macro is working fine. after a couple of hours I found some columns were hidden in the original work book :banghead: thank you everyone particularly Brian smiler44

Brian, no, I'm stumped. I cant work this out For r2 = 1 To lrws2 'rescan For r1 = 2 To lrws1 If ws1.Cells(r1, 2) = ws2.Cells(r2, 1) Then ws1.Rows(r1).EntireRow.Hidden = False Exit For End If Next r1 Next r2 I thought: r2 was column 2 on sheet 1 r1 was column1 on sheet 2...

Brian, thank you. Not sloppy at all I will keep macro 1 an use it but was just checking it was not required in conjunction with macro2. could you help me a bit more? agh, hang on, I may have it, let me go try and I'll be back smiler44

gggrrrr typed a reply but it timed out and I lost the post. Brian, thank you. I think I understand but can I check. macro2 will display on sheet1 1 instance of a word in column B if that word is found on sheet2 column A. Do I need macro 1. Thank you for taking the time tom put this...

sorry I'm lost. smiler44

I did want to automate but accidently finding this did the job...of sorts. when I click away from excel the drop down list of the filter closes so it was not quite the solution I thought it was smiler44

thank you for the replies. By accident I found an easy way. Not a way that I had expected but never the less will do what I need ok, I use a macro to filter my spread sheet to show only the rows that contain pen, car, boat etc in column whatever. could be about 25 criteria's and of course be...