Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I was struggling to find a VBA solution that I could extract data from all the rows of a filtered table. With my failures, I resorted to a Google search and found what I thought might/should work, but I clearly don't know enough to have realized it truly wasn't the solution. I present you my code:
The code I thought would step through each row of the filtered data, assigning values extracted from that current row to variables, to be later placed in a data holding area in another worksheet. This is not happening.
What is happening is it only refers to data in row 1, the header row so the values of segnum, segtxt and segtype are always the same and equal to the header values in row 1 respectively.
Essentially, I need my loop to step through each visible row in the table with the exception of row 1. I'm not certain how to create that loop.
VBA Code:
Sub process_segments()
Dim filter_rng As Range
Dim drow as integer
Dim segnum as integer
Dim segtype as string
Dim segtxt as string
With ws_form
ws_segments.AutoFilterMode = False
With ws_segments.Range("A1")
.AutoFilter field:=1, Criteria1:=zone
.AutoFilter field:=2, Criteria1:=corridor
'.AutoFilter field:=4, Criteria1:=segment
End With
With ws_segments
Set filter_rng = .Range("A:F")
drow = 9
For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)
segnum = .Range("C" & rw.Row).Value
segtxt = .Range("E" & rw.Row).Value
segtype = .Range("F" & rw.Row).Value
'copy data to another worksheet first data line is cell A2
ws_form.Range("A" & drow).EntireRow.Insert
ws_form.Range("G" & drow).Value = segtype
ws_form.Range("H" & drow).Value = "[" & segnum & "]"
ws_form.Range("J" & drow).Value = segtxt
drow = drow + 1
Next rw
End With
End With
End Sub
The code I thought would step through each row of the filtered data, assigning values extracted from that current row to variables, to be later placed in a data holding area in another worksheet. This is not happening.
What is happening is it only refers to data in row 1, the header row so the values of segnum, segtxt and segtype are always the same and equal to the header values in row 1 respectively.
Essentially, I need my loop to step through each visible row in the table with the exception of row 1. I'm not certain how to create that loop.