Add to VBA code to increase efficiency

[baz9d93]

Thank you for taking time to read this.

1. I created a post here Modify VBA to select Cells with Red Font Copy only cells not row which was anwered by a kind member- it was based on a tutorial I was learning from and it now does what was required

Using This Code

        Sub baz9d93()
Dim ce As Range, lastrow As Long, lastcol As Long, i As Long
Dim wsA As Worksheet, wsH As Worksheet

Set wsA = Sheets("All Transactions")
Set wsH = Sheets("Highlighted Transactions")

lastrow = wsA.Range("A" & Rows.Count).End(xlUp).Row
lastcol = wsA.Cells.SpecialCells(xlCellTypeLastCell).Column
Application.ScreenUpdating = False

For Each ce In wsA.Range(wsA.Cells(2, 2), wsA.Cells(lastrow, lastcol))
If ce.Font.Color = RGB(255, 0, 0) Then
ce.Copy wsH.Range(ce.Address)
wsH.Range("A" & ce.Row).Value = wsA.Range("A" & ce.Row).Value
End If
Next ce

lastrow = wsH.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
If wsH.Cells(i, 1).Value = "" Then wsH.Cells(i, 1).EntireRow.Delete
Next i

wsH.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

I wanted to ask how to make it more efficient

In the actual worksheet that I have it has 35000 rows and 60 Columns with different values - the way I am doing it now is using Find Replace entering the Word I need to find - in each cell - then CTRL A - then changing the font color to Red then using this Code you he kindly provided. So I need to run this process 60 times


Is it possible for me to get this done faster by putting in Sheet 1 in another Column for example Col E all the values I need it to look for and then Put them each in a new Sheet?


Or can anyone kindly suggest a more efficient way of doing this task. thank you

 

[offthelip]

When designing an excel system for speed, it is really valuable to understand what are the things that can make a worksheet either slow to calculate of slow when running VBa.

Your system is using font color to trigger a logical test. Unfortunately to test the color of a cell you need to access each cell individually to test each cell. This is inevitably going to be slow when there are a lot of cells.

However this isn’t true for the values in a cells, this can be done very quickly by loading the entire worksheet into a variant array.

Try these two different macros, the first one tests the color in range A1to A1000 ( I put values in these cells and changed the color to red)

The second one tests the value in the range by first loading the range into a variant array.

Note the first loop does 20 times the test and the second loop does 2000 times the test

On my machine the first macro take 62 milliseconds and the second one take 46 milliseconds. So even though it is doing 100 times more iterations it is still faster.

The point of my post is that color should only be used for help humans beings looking at the worksheet, it should never be used as a method of storing information. It is almost always faster to store the information as a value in a cell and then use conditional formatting to help with the display.

So I think you are going to find it difficult to really speed up your system all the time you are using color as a logical flag. Can you redesign your system to avoid testing for color??

Sub slow()
tt = Timer()
For j = 1 To 20
For i = 1 To 1000
  ts = Cells(i, 1).Font.Color
Next i
Next j
ts = Timer
 MsgBox (ts - tt) * 1000
End Sub

Sub fast2()
tt = Timer()
inarr = Range("a1:a1000")
For j = 1 To 2000
For i = 1 To 1000
  ts = inarr(i, 1)
Next i
Next j
ts = Timer
 MsgBox (ts - tt) * 1000
End
End Sub

 

[baz9d93]

Thank you for your kind prompt reply

I am very sorry I should have mentioned the font color was only used because I was learning with a tutorial - color is not a requirements and worksheet does not have this all just normal black fonts.
After spending days that tutorial was the closest thing I could find to my requirement. I was using the Find All and then changing the Text color to red then using the VBA to put all those values in another sheet

The list array would have words for example Weight Height Length or Weight per Portion so I need these seperated into a sheet on there own or Better still if possible in 1 sheet but all cells with Weights in same one column all cells with Heights in 1 Column with each attribute in the list array having its own separate column. thank you

 

[offthelip]

I suggest you have think about what your system will look like without color and the post a new question, (and picture) to try and solve whatever you other problem is. From your current decription it is not at all clear what you are trying to do.. I am hoping that taking the color bit out will make it clearer .

 

[Norie]

Are you trying to create separate lists the codes in column A based on the values in the other columns?

For example a list of all the codes from column A which have the value 'weight' on the same row.

 

[baz9d93]

please check updated mockup image -

 

[baz9d93]

i created a new post as suggested here hopefully more clearly explained

VBA to find and Copy to new Sheet​

 

[Norie]

Is there a list of the headings, e.g. Weight, Height?