please help with macro - driving me crazy

[Serengeti]

Hello, I need the macro below to read alphanumerical values in the cells in column F. Currently it only reads letters (of the alphabet). The cells in column F can contain either letters or numbers or both - so the macro needs to look for any combination and then cut and paste the data. Appreciate the help,thank you for your time.

Code: Select all

Sub Test()
    Dim ShSource As Worksheet
    Dim ShTarget As Worksheet
    Dim Rng As Range
    Dim NextCell As Range
    Set ShSource = Worksheets("Current")
    Set ShTarget = Worksheets("Archive")
    With ShSource
        Set Rng = .Range("F1:F" & .Range("F" & .Rows.Count).End(xlUp).Row)
        If WorksheetFunction.CountIf(Rng, "> A") = 0 Then Exit Sub
       
    End With
    With ShTarget
        Set NextCell = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
    End With
    Rng.AutoFilter
    Rng.AutoFilter Field:=1, Criteria1:="> A"
   
    Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 1)
    With Rng.SpecialCells(xlCellTypeVisible).EntireRow
        .Copy NextCell
        .Delete
    End With
    ShSource.AutoFilterMode = False

[Don]

Hi and welcome!

Can you include a sample worksheet that has one tab with the current setup and one tab setup like you want it to run?

[Serengeti]

Hello Don, currently I am away from my work desk but I can explain what I need. In the workbook there are two sheets. I enter the data on the first sheet in rows that expand across columns A to H. If the cells in column F contain alphanumerical data then the entire row is cut and pasted from sheet 1 to sheet 2.
The macro currently does work except it only reads the alpha values and not the numeric - I need it to read both.

Thank you for the welcome. I hope I have explained enough of what I need.

[Sisyphus]

Hi,

Your problems stems from the expression "> A". What is greater than "A"? You can enter this question directly into the Immediate Window of the VBE - is 1 greater than A:

Code: Select all

   ? "1" > "A"


Enter a return (Enter) to get answer, False. 1 is NOT greater than A. You can expand this test to any strings that occur in your Source sheet. The result will be that all numbers and most non-numeric characters (like periods, commas, brackets, but not slashes) are greater than "A", but "A" is also not greater than "A" (because it is same). "A" is also greater than "A1". In fact it is greater than any combination of characters and numbers that you might try. In other words, you never test for alphanumeric values. Instead, your code manages only to distinguish between values that start with a number and those that start with a character.

The first occurrence of the >A test is in this code:

Code: Select all

    With ShSource
        Set Rng = .Range("F1:F" & .Range("F" & .Rows.Count).End(xlUp).Row)
        If WorksheetFunction.CountIf(Rng, "> A") = 0 Then Exit Sub
    End With


Allow me digress at this point.
1. I suggest that the concatenation of strings to address ranges is complicated and cumbersome.
A single cell is defined by Cells([Row], [Column]), a range by the two cells that mark its beginning and end:
Range(Cells(1,1), Cells(2,3)) = Range("$A$1:$C$2").
The advantage is that you can calculate both, columns and rows, and Excel works faster with numbers than with text strings.
So, you have

Code: Select all

Set Rng = .Range(.Cells(1,6), .Cells(.Range(.Cells(.Rows.Count).End(xlUp).Row, 6))


If that is confusing the reason must be looked for in the fact that it is the product of confusion. You can determine the last row in column F by this formula: R = ShSource.Columns(6).Cells(65536).End(xlUp).Row
In your formula you have substituted .Rows.Count for 65536. In fact, .Rows.Count, by another means, is the number of the row you are looking for. Your formula will always return the number you put in: .Rows.Count. Since you want the last row that was used in column F (= 6) your formula should look like this:

Code: Select all

Set Rng = .Range(.Cells(1, 6), .Cells(.Columns(6).Cells(65536).End(xlUp).Row, 6))


As you can see, the clearer expression helps you avoid the error you made because each Cell is specified by exactly two parameters, Row and Column, and two cells define the range. Just be sure you put the right number of periods when using the system in a With statement because an extra or missing dot makes all the difference.

You use the range Rng to run the COUNTIF worksheet function whose result is subject to some doubt since it just recognizes numbers larger or smaller than A, i.e. numbers that start with a numeric value as opposed to those that start with a letter. I think, by the time you finish re-designing your macro you will not need this test. What you will need is the last row of your data. Like

Code: Select all

Dim LastRow as Long
LastRow = ShSource.Columns(6).Cells(65536).End(xlUp).Row


Your code then proceeds to apply a filter using a faulty criterium, to wit, "> A". Now, as you have stated, your code can be made to work if the criterium can be made to work. I don't think that it can be made to work. Therefore I am left with no choice but to scrap the idea of using the AutoFilter. Instead, I suggest a simple loop:

Code: Select all

For R = FirstRow to LastRow
    ' Examine and act
Next R


If you can't make the AutoFilter work you also can't make the COUNTIF function work. That is why I say that you will most probably not need the test at all. Moreover, the For .. Next loop, if it comes up empty, will probably be finished almost as quickly as the test. If you have a large volume of data most of which have been tested before, I suggest to make a record of the last row previously checked and make that the first row for the next check. You may use a spare cell in the worksheet and format it as invisible (NumberFormat = ";;;") or you put the value in a Custom Property.

Now, you want to test if a number is numeric or not. The best way is the one that is most simple:

Code: Select all

If IsNumeric(ShSource.Cells(R, 6).Value) Then
    ' .Value contains no letters
End If


Your target cell is a single cell in column A of the Target Worksheet. You don't need to work with an Offset there if you have a row number. The next row is +1. So, your target cell is always

Code: Select all

With ShTarget
    .Cells(.Columns(1).Cells(65536).End(xlUp).Row + 1, 1)
End With


This is too much baggage to carry around. Therefore, I suggest you establish second row counter. I usually use Rt (Row Target) for that purpose. Remember to declare it as Long.

So, put it all together:

Code: Select all

Rt = ShTarget.Columns(1).Cells(65536).End(xlUp).Row
With ShSource
    For R = FirstRow to LastRow
        If Not IsNumeric(ShSource.Cells(R, 6).Value Then
            Rt = Rt + 1
            .Rows(R).Copy ShTarget.Cells(Rt, 1)
            .Rows(R).Delete
            R = R - 1
        End If
    Next R
End With


I hope the little trick with R = R - 1 will do the job as intended. When you test the code watch for what happens after the last row in your data has been reached.

Voluminous answer, even by my standards, you might say. You are probably right, but who wants you crazy?
Instead, I hope to have been of some help.
Have a great day!