Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed Col

Thank you so much for your help. I am getting error on the below row
.AutoFilter i, "<>0", , "<>"

Yes perfectly working as expected in sample file
But in my file its not working both Codes i am now getting error of "Variable not found" in below row
"With Sheet2"

and second code
sheet2.Range

Another option

Please Login or Register  to view this content.

Thank you so much. The Last Code is working perfectly. Thanks for your help

Before Running the Macro. First i am creating "worksheet in the name of "Output" then only its working.
and is it possible to modify the code by selecting the data and run? instead of permanently fixing the cell reference of Cell A3 to F3. if possible please help on this. existing output enclosed Attachment 674830

Hi This code is working fine and helped. But data in A2 is for your reference purpose i added. its not there in actual. if remove row 2 then its skipping to next row for copy. Can you please change the code to start with A3. and is it possible to modify the code by selecting the data and run? instead of permanently fixing the cell reference of Cell A3 to F3. if possible please help on this

Mr.nankw83 code is working.

for your code though i created Sheet2 and Output sheet but its not working.its showing error of variable not defined.Attachment 674865

I have adjusted the code to ignore the first row which I think you kept only for descripting your issue. Also, the code is dynamic now where you can place the data in any cell & select at least one cell of the data then run the code … of course results will be in sheet Output … I've attached a sample file for your reference. Let me know how it goes

Please Login or Register  to view this content.

@sintek

It doesn't matter as I have used the following line in my loop for number of columns

Please Login or Register  to view this content.

Output sheet is fixed at 4 columns irrespective of number of columns in original data set

Hi nankw83

Perfectly working as expected. now its working any sheet and any rows. Thank you so much for your help

Glad to help & you are welcome

can i have one more help please. on the out put column D, can you please separate negative and positive number separately. Means Column D for Possitive numbers and Column E for Negative numbers.

@sintek

@ayyappan80

Try below code

Please Login or Register  to view this content.

Hi, nankw83

Thanks its working fine. i am getting positive and negative separately. once again thanks for your help.

Hi, nankw83
On the Column E where we updated negative amount. I want to shown as positive numbers. (After updating the number (-) to be removed)

the below changes will not work for that?

b(i, 5) = IIf(a(x, y) < 0, a(x, y), "").SpecialCells(xlCellTypeConstants, 1)

Try like this

Please Login or Register  to view this content.

thanks nankw83. Its worked.

Hi Nankw83 I want to little modify the code. can you please help

on the below line of Code need to make one change

b(i, 1) = a(1, Y)
b(i, 2) = "'" & "000"

if i,1 ="101" then i,2 to be updated as "002",
if i,1 ="102" then i,2 to be updated as "003"

else it to be updated by default "000"

Currently the above line of code is updating by default "000"

Hi Nankw83 could not get the result. attached sample file for your reference

Hi Nankw83

Perfectly working thank you so much for your help.