List text in column without spaces from a spreadsheet if numerical values greater than 0

[Dan1000]

I have a spreadsheet with text strings in some of one column with numerical values against the text in a neighbouring column, and with '0' values or blank cells against the empty text cells. I would like to extract the text data to another spreadsheet based on the numerical values being above 0 using VBA code so that the text values read from top to bottom without any empty cells and leaving them in alphabetical order.

Any help would be gratefully appreciated.


Dan

 

[mumps]

In which column are the text strings and are they already in alphabetical order? Are the "0" values and blanks in the neighbouring column entered manually or the result of a formula?

 

[Dan1000]

Hi Mumps

Specifically...

The text is in alternate cells in column L and are not necessarily already in alphabetical order,
The numerical values are in column M,
The numerical values sum up the random numerical values in each respective row some of which add up to 0 but its the ones that have 1 or more I need to extract


Regards

Dan

 

[aqeelnokia99]

batter you post you data and result that u want

 

[mumps]

Try:

Sub Dan1000()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("L1:M" & LastRow).AutoFilter Field:=1, Criteria1:="<>"
    Range("L1:M" & LastRow).AutoFilter Field:=2, Criteria1:=">0"
    Range("L2:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Range("L1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Data will be copied to Sheet2.

 

[Dan1000]

Brings up runtime error 1004 and inserts the header and the first item several times into sheet2?


Dan

 

[Dan1000]

Example: sheet1

[TABLE="width: 174"]
<colgroup><col width="87" span="2" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl66, width: 87, bgcolor: #D9D9D9"]Item1[/TD]
[TD="class: xl66, width: 87, bgcolor: #D9D9D9"]Quantity[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text1[/TD]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text2[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text4[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text3[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text5[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text6[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text7[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text9[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Text10[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Outcome: sheet2

[TABLE="width: 174"]
<colgroup><col width="87" span="2" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl66, width: 87"]Text1[/TD]
[TD="class: xl65, width: 87, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66"]Text6[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Text7[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Text10[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Note, first column is in alphabetical order and does not have a header. I need to locate the list a few rows down and a few columns across -first text item in H13 - and so some advice on how to adjust / make this happen would also be helpful


Dan

 

[mumps]

This information

few rows down and a few columns across

doesn't really help. The macro needs precise information. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.