Macro to list only choosen cells

[TorrO]

Hi

can I get some help to make a macro?

The macro that reads column J (Pick) if there is an 'x', then return adjacent cell value in column K(Data) to a list in column M (result)

SIA F calculation program V1.xlsx
	J	K	L	M
3	Pick	Data		Result
4	x	Car		Car
5		House		Cat
6	x	Cat		Dog
7	x	Dog
8		Cabin
Std Txt 2020.09.13

 

[mumps]

Try:

Sub torrO()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim pick As Range
    For Each pick In Range("J4:J" & LastRow)
        If pick = "x" Then
            Cells(Rows.Count, "M").End(xlUp).Offset(1) = pick.Offset(, 1)
        End If
    Next pick
    Application.ScreenUpdating = True
End Sub

 

[TorrO]

mumps

Thanks, worked like a charm!

 

[mumps]

You are very welcome.

 

[Rick Rothstein]

Here is another macro (a non-looping one-liner) that should also work...

Sub GetValuesMarkedWithX()
  Intersect(Range("J4:J" & Rows.Count).SpecialCells(xlConstants).EntireRow, Columns("K")).Copy Range("M4")
End Sub

 

[TorrO]

Thanks Rick Rothstein

Your macro also copied format from original text. That was a relief since this is a sheet that i try to automate as much as possible.