Is this possible?

[MrData]

Have to admit defeat and conract you people at MrExcel again. Have done a number of searches and am unable to find a solution.

I have a spreadsheet that has many rows of data with approximatly 30 columns being used.

In cell AZ1 there is a value that can be amended. Depending on the value in this cell I would like to filter the rows that match this value (column B is where they will match)

Based on this I would like to use a userform to update the contents of the rows that have been filtered. The updating will be done on a manual basis.

I know this is possible in Access but I need a similar solution using Excel. Can anyone please help a desperate person?

 

[erik.van.geit]

Hi, MrData,

For the first part of your question:

When you record a macro and then autofilter you get something like
Selection.AutoFilter Field:=1, Criteria1:="b"

This can bring you to the code
Columns(2).AutoFilter Field:=1, Criteria1:=Range("AZ1")

For the second part (Userform etc...) you could give more details ...

kind regards,
Erik

 

[MrData]

Thanks I can follow the code that you have supplied. With regard to the second part here goes. I currently have a userform (with lists, combo box etc.) which places data in a spreadsheet. An identifier is also entered that links various rows of data together. This can be found in column B and can appear on more than one instance.

I need a second user form that will bring up the rows of data in order so that they can be amended in a userform. The amended records then need to overwrite the original data. I've seen something in Access, but we only use Excel in work so its got to be in Excel format.

Example: The spreadsheet has 43,000 rows of data. The identifier in column B reads 0003 and by filtering this shows 10 rows. Somehow I need a Useform to show these 10 rows (column A:AF), allow the user to update manually and for the original data to over written.

Hope this explains things better, if not let me know and I'll try again.

 

[erik.van.geit]

OK,

You need to "feed" listboxes. (Listbox.Additem ...) There are numerous threads on that: search for "populating listbox".

You need to populate with only the visible cells:
Range("yourrange").SpecialCells(xlCellTypeVisible)
unfortunately I didn't get it to work with this feature
but this example is working
it selects the visible rows within a range

Sub select_visible_rows_in_range()
Dim cc As Integer
Dim rrr As Range
Dim i As Integer

cc = Range("yourrange").Columns.Count
Set rrr = Range("yourrange")

    For i = 1 To rrr.Cells.Count Step cc
      If rrr(i).EntireRow.Hidden = False Then
      rrr(i).Resize(1, cc).Select
      Application.Wait Now + TimeValue("00:00:01")
      End If
    Next i
End Sub

you could adapt this code or another to feed your listbox

does this give you a start?

kind regards,
Erik

 

[MrData]

Erik,

Will give it a go on Monday, as I've got problems with my computer.

Thanks

 

[erik.van.geit]

Mike,

No problem.
I was just getting problems with my e y e s.

kind regards,
Erik