Lastrow definition problem

YounesB3,

If those values started V52 and went to V61 and you wanted the row number 58 which is the row "g" is in, you could use the following:

LastRow3 = Range("V52:V" & Rows.Count).Find("blank").Row - 1
MsgBox LastRow3

Cross-posted
http://www.mrexcel.com/forum/excel-q...n-problem.html

@tigeravatar

I guess it would have worked, but I haven't had the opportunity to try it because my plan didn't work lol...

Let me explain: I apply a filter in column W. I need to copy data from column U to column C (at the end where the first blank cell appears.

This is the code I use so far (i replaced with "?" all characters which could be confidential:

    ActiveSheet.Range("$A$51:$W$9000").AutoFilter Field:=23, Criteria1:= _
        "0ADD ???"
    Range("W51").Select
    Cells.Find(What:="0add", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -2).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("C51").Select
    Cells.Find(What:="blank", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("$A$51:$W$412").AutoFilter Field:=23

I highlighted in yellow the thing which needs to be changed..

At first, I tried to find a blank cell, but because of the filter, it didn't work. For instance, if I had lines 167, 289, 384 & 487 showing (because of the filter) and the last filled line was 550, when I did the find "", it didn't go to line 551, but instead changed column and went to the first empty cell.

Then, I added the formula : =if(isblank(A??), "blank", "") from line 550 to something like 1000 or 1250. That didn't work either because when I apply the filter, the last line shows as 1000 (or 1250) instead of 550.

I'm not sure if I'm being clear or not. If you need more explaination, please advise.

Any ideas on how I could do the copying from U to C?

Cross-post: http://www.msofficeforums.com/excel-...html#post40618

Don't you just want to copy the results after the filter is applied? Is there any reason you don't want that?

Here's a quick step of what I do manually (and want to automate).

- I apply the filter in column W.
- Results appear in column U.
- I take the results from U and paste it at the END of column C (first blank cell).

I can't do the last one in a macro. Note the line number always changes due to constant updating.

As explained, I already tried searching for "", but for some reason it doesn't work.
I also tried to input "blank" from the first blank cell to X, but if I do that and apply the filter, all the words "blank" are not going to show up (so I can't search for the word "blank" with the macro).

Thanks for any help you can provide!

Something like this?

Sub tgr()
    
    Dim rngDest As Range
    Dim rngCopy As Range
    
    Set rngDest = Cells(Rows.Count, "C").End(xlUp).Offset(1)
    
    With Intersect(ActiveSheet.UsedRange, Columns("W"))
        .AutoFilter 1, "0ADD ???"
        On Error Resume Next
        Set rngCopy = Intersect(.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow, Columns("U"))
        On Error GoTo 0
        .AutoFilter
    End With
    
    If Not rngCopy Is Nothing Then
        rngCopy.Copy
        rngDest.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    
    Set rngDest = Nothing
    Set rngCopy = Nothing
    
End Sub

This pratically works! I just had to modify ".AutoFilter 1" with ".AutoFilter 23" (for column W).

The only fix which I don't know how to do is, it takes the whole column U and past is as values. The thing I didn't mention in previous post is that the filters are applied from line 51 and I would like to take the data from line 52 (in column U) to the end of column U. How do I modify the macro?

Also, could you explain a bit what you did in the macro so I understand more? :P

Thanks a lot!

YounesB3,

Can you post the code you're using?

The same as before, but I replaced your code with the old one I had:

    Windows("A.xlsm").Activate
    Sheets("AA").Select
    Range("A51:W51").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Windows("B.xlsm").Activate
    Sheets("BB").Select
    Range("D3:D9000").Select
    Selection.Copy
    Windows("A.xlsm").Activate
    Sheets("AA").Select
    Range("U52").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    LastRow = Range("U" & Rows.Count).End(xlUp).Row
    Range("W52").Select
    Selection.AutoFill Destination:=Range("W52:W" & LastRow)
    LastRow2 = Range("Q" & Rows.Count).End(xlUp).Row
    Range("V52").Select
    Selection.AutoFill Destination:=Range("V52:V" & LastRow2)
    
    
    Dim rngDest As Range
    Dim rngCopy As Range
    
    Set rngDest = Cells(Rows.Count, "C").End(xlUp).Offset(1)
    
    With Intersect(ActiveSheet.UsedRange, Columns("W"))
        .AutoFilter 23, "0ADD ???"
        On Error Resume Next
        Set rngCopy = Intersect(.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow, Columns("U"))
        On Error GoTo 0
        .AutoFilter
    End With
    
    If Not rngCopy Is Nothing Then
        rngCopy.Copy
        rngDest.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    
    Set rngDest = Nothing
    Set rngCopy = Nothing

Honestly I would be very surprised if that code doesn't error on the .AutoFilter 23, "0ADD ???" line.

I used 'With Intersect(ActiveSheet.UsedRange, Columns("W"))' to explicitly only "select" column W. Therefore, there is only 1 field, which is why it is .AutoFilter 1 instead of .AutoFilter 23.

If you need the filter applied starting at row 51 so that it only grabs data from row 52 down, just update the With line:

    With Intersect(ActiveSheet.UsedRange, Range("W51:W" & Rows.Count))
        .AutoFilter 1, "0ADD ???"
        On Error Resume Next
        Set rngCopy = Intersect(.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow, Columns("U"))
        On Error GoTo 0
        .AutoFilter
    End With

When I use it with 1 instead of 23, it applies the filter in column A.

Except from that, with the modification you applied, it now works! Thanks a lot!

Can you explain a bit the vb code you used?

    'Define the range that will be worked with
    With Intersect(ActiveSheet.UsedRange, Range("W51:W" & Rows.Count))
        
        'Apply the filter
        .AutoFilter 1, "0ADD ???"
        
        On Error Resume Next    'Just in case there are no results
        'Get the Visible cells now that the filter has been applied
        'Look that those same cells' row and get the values from column U
        Set rngCopy = Intersect(.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow, Columns("U"))
        On Error GoTo 0         'Remove the On Error Resume Next option
        
        'Remove the filter
        .AutoFilter
    End With

Thanks a lot!

@tigeravatar

I have one last problem (I hope). Somehow, when I filter to "0ADD ???", it doesn't only shows "0ADD ???" for some reasons.

Example of what it sometimes do:

Column U	Column V	Column W
???1	???A	0ADD ???
???2	???B	0ADD ???
???3	???C	0ADD ???
???4	???D	0ADD ???
???5	???E	0ADD ???
???6		???%
???7		0ADD ???
???8		???@
???9		???&
???7		0ADD ???
???11		???$

Now, with the present macro, it would take everything in column U. What I want to do is to take everything in column U when it says column W "0ADD ???". I'm not sure why the filter doesn't work properly...

When using an autofilter (either manually or through VBA code), the question mark character is considered a wildcard, which is probably why you're getting some results you didn't expect. I was under the impression you wanted to use the wildcard character. Since that is not the case, here is an alternate method using Evaluate() instead of .AutoFilter

    Dim rngDest As Range
    Dim rngCopy As Range
    
    Set rngDest = Cells(Rows.Count, "C").End(xlUp).Offset(1)
    
    With Intersect(ActiveSheet.UsedRange, Range("W51:W" & Rows.Count))
        On Error Resume Next
        Set rngCopy = Intersect(Range(Join(Filter(Application.Transpose(Evaluate("If(" & .Address & "=""0ADD ???"",Row(" & .Address & ")&"":""&Row(" & .Address & "))")), False, False), ",")).EntireRow, Columns("U"))
        On Error GoTo 0
    End With
    
    If Not rngCopy Is Nothing Then
        rngCopy.Copy
        rngDest.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    
    Set rngDest = Nothing
    Set rngCopy = Nothing

The Evaluate() method is a bit more advanced, but shouldn't use the question mark as a wildcard character.

If you're filtering for "0ADD XYZ" that shouldn't cause any issues. I'd have to see an example workbook experiencing the problem to diagnose, because I am unable to duplicate the problem with the information that I have.

I previously did an example file. You can find it here: Attachment 187313

Of course, I replaced all the elements which I can't put on the net due to company policies.

In the example I posted, it doesn't do what I mentionned in the topic. I messed around with the example file and created a file when it does happen: Attachment 187312

As you can see, the problem occurs when there is more data in column U & W then number of data in column A to Q & V. I'm not sure why though...

I am unable to view that file. I am at work and cannot get to that site, please use the attach file method provided by this forum.

To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

@tigeravatar

Oups sorry about that, I didn't see it (the site was actually the only one not blocked over here ;p). The files are now attached.

Example.xlsx

Example 2.xlsx

Ah, the problem was that you already had a filter area applied. I included to remove any filters before applying mine and that seems to have taken care of it:

    Dim rngDest As Range
    Dim rngCopy As Range
    
    Set rngDest = Cells(Rows.Count, "C").End(xlUp).Offset(1)
    
    With Intersect(ActiveSheet.UsedRange, Range("W51:W" & Rows.Count))
        .Parent.AutoFilterMode = False
        .AutoFilter
        .AutoFilter 1, "0ADD ID"
        On Error Resume Next
        Set rngCopy = Intersect(.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow, Columns("U"))
        On Error GoTo 0
        .AutoFilter
    End With
    
    If Not rngCopy Is Nothing Then
        rngCopy.Copy
        rngDest.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
    
    Set rngDest = Nothing
    Set rngCopy = Nothing

Thank you so much, I'll try that tomorrow with the new data and give you some news! :P

@tigeravatar
Everything since to work perfectly now thanks to you!

I'm not sure if I should start another thread or not, but since it's the same file here it is (cross-posted in mr excel & ms office forums; but none answered in some days):

So, I had a superb macro working before:

Sub HideRows()
Intersect(Range("Range1").SpecialCells(xlCellTypeBlanks).EntireRow, _
Range("Range2").SpecialCells(xlCellTypeBlanks).EntireRow) _
.EntireRow.Hidden = True
Intersect(Range("Range3").SpecialCells(xlCellTypeBlanks).EntireRow, _
Range("Range4").SpecialCells(xlCellTypeBlanks).EntireRow) _
.EntireRow.Hidden = True
Intersect(Range("Range5").SpecialCells(xlCellTypeBlanks).EntireRow, _
Range("Range6").SpecialCells(xlCellTypeBlanks).EntireRow) _
.EntireRow.Hidden = True
Intersect(Range("Range7").SpecialCells(xlCellTypeBlanks).EntireRow, _
Range("Range8").SpecialCells(xlCellTypeBlanks).EntireRow) _
.EntireRow.Hidden = True
Intersect(Range("Range9").SpecialCells(xlCellTypeBlanks).EntireRow, _
Range("Range10").SpecialCells(xlCellTypeBlanks).EntireRow) _
.EntireRow.Hidden = True
End Sub

This would hide rows of 5 different tab in a worksheet between 2 columns. The problem is, I now had to put some formulas to automate the file and the macro doesn't work anymore even if the value in the cell = "". Is it possible to change the above code to work for cells which are either blank or = ""? Right now it only works for blank rows with no formulas at all

Thanks a lot!

This is different enough that it should be a new thread. You'll want to make sure you provide the links to the crossposts also