Extract non blank rows and adjacent data

[slam]

Hi all,

Would greatly appreciate it if someone could lend a hand with a problem I have.

I have a range, A9:G200. I'm looking for non-blank rows in column G. When a value exists (dollar amount), I then want that value displayed in N3, and I want the associated value from column A displayed in L3, and the value from column B displayed in M3. The next non-blank value in column G would then be displayed in N4, L34, and M4 respectively.

The same value could be displayed in column G more than once, but I would want all values listed in column N with the data from the other respective columns.

Thank you

 

[alansidman]

Option Explicit


Sub slam()
    Dim lr As Long, c As Range, rng As Range
    lr = Range("G" & Rows.Count).End(xlUp).Row
    Dim start As Long, myrow As Long
    Set rng = Range("G9:G" & lr)
    start = 3
    For Each c In rng
        If c <> "" Then
            myrow = c.Row
            Range("N" & start) = c
            Range("L" & start) = Range("A" & myrow)
            Range("M" & start) = Range("B" & myrow)
            start = start + 1
        End If
    Next c
    MsgBox ("Complete")
End Sub

 

[igold]

How about this:

Sub noBlanks()


    Dim arr
    Dim i As Long, rw As Long
    
    rw = 3
    arr = ActiveSheet.Range("A9:G200")
    For i = LBound(arr) To UBound(arr)
        If arr(i, 7) = "" Then GoTo Skip
        Cells(rw, 14) = arr(i, 7)
        Cells(rw, 12) = arr(i, 1)
        Cells(rw, 13) = arr(i, 2)
        rw = rw + 1
Skip:
    Next
    
End Sub

 

[Peter_SSs]

Here is a formula approach if you are interested in that method.

Formula in N3 is copied down
Formula in L3 is copied across to M3 and down

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2
3												Col A Rw 9	Col B Rw 9	200
4												Col A Rw 12	Col B Rw 12	35
5												Col A Rw 13	Col B Rw 13	200
6												Col A Rw 14	Col B Rw 14	89
7												Col A Rw 15	Col B Rw 15	52.33
8												Col A Rw 18	Col B Rw 18	145
9	Col A Rw 9	Col B Rw 9					200
10	Col A Rw 10	Col B Rw 10
11	Col A Rw 11	Col B Rw 11
12	Col A Rw 12	Col B Rw 12					35
13	Col A Rw 13	Col B Rw 13					200
14	Col A Rw 14	Col B Rw 14					89
15	Col A Rw 15	Col B Rw 15					52.33
16	Col A Rw 16	Col B Rw 16
17	Col A Rw 17	Col B Rw 17
18	Col A Rw 18	Col B Rw 18					145
19
Non blank rows

 

[slam]

Thanks to all who replied. I went with the formula option, and it works perfectly.

 

[Peter_SSs]

Thanks to all who replied. I went with the formula option, and it works perfectly.

You are welcome. Glad you got something that suited you.