• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Excel Macro

Hi,  

I have the macro below which I am trying to develop to help sort the excel file attached, the macro works but I would like the result to not show any 0 or – figures in column C (ORDER QUANTITY) is this possible?

Sub orderreport()
Dim sr As Boolean, i As Long, rng As Range
ActiveSheet.Copy
ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
Range("J1:S1").EntireColumn.Delete
Range("C1:G1").EntireColumn.Delete
sr = Range("A3:A" & Rows.Count).Replace("SELECT", "")
Range("A3:A" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Set rng = Range("A3").CurrentRegion
sr = rng.Sort([a1], xlAscending)
For i = rng.Row + rng.Rows.Count To 3 Step -1
If Cells(i + 1, 1) <> Cells(i, 1) Then Cells(i + 1, 1).EntireRow.Insert
Next i
End Sub
 Order-data.xls
0
AlexAmbler
Asked:
AlexAmbler
1 Solution
 
Shanan212Commented:
=IF((G4-F4)<=0,"",G4-F4)

Open in new window


Why not put the above in H4 cell in your Order-Data file? This would prevent any zeros and negative numbers.
0
 
Saqib Husain, SyedEngineerCommented:
Add this line after the For statement

If Cells(i, 3) <= 0.5 Then Cells(i, 3).EntireRow.Delete
0
 
[ fanpages ]IT Services ConsultantCommented:
How about adding this code line just before the "End Sub"?

Columns("C").NumberFormat = "0;"""";-0"

BFN,

fp.


Columns("C").NumberFormat = "0;"""";-0"

That is...

Sub orderreport()
Dim sr As Boolean, i As Long, rng As Range
ActiveSheet.Copy
ActiveSheet.UsedRange.Cells.Value = ActiveSheet.UsedRange.Cells.Value
Range("J1:S1").EntireColumn.Delete
Range("C1:G1").EntireColumn.Delete
sr = Range("A3:A" & Rows.Count).Replace("SELECT", "")
Range("A3:A" & Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Set rng = Range("A3").CurrentRegion
sr = rng.Sort([a1], xlAscending)
For i = rng.Row + rng.Rows.Count To 3 Step -1
If Cells(i + 1, 1) <> Cells(i, 1) Then Cells(i + 1, 1).EntireRow.Insert
Next i

  Columns("C").NumberFormat = "0;"""";-0"

End Sub

Open in new window

0
 
AlexAmblerAuthor Commented:
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now