Query Data With VBA Filter

Asked

Viewed 936 times

0

You guys, good night

I have a problem in VBA, can help me please. Follow:

I have an excel with Sheet1 and Sheet2. At Sheet2 I have a list of clients where I have the following data: Name, City and Age.

Exemplo dos dados

In VBA, I need to copy the data of Sheet2 and paste in Sheet1 based on some filters, for example where the City is equal to "São Paulo".

An example of the result would be, in Sheet1, to copy the data where the city is equal to São Paulo, so would João and Manuel.

Some points that can be a hindrance: 1 - I do not know the number of lines of the Sheet2 register. For example, as shown above, the register has 4 records, but could have 200. In that case I don’t know beforehand how many lines you have.

2 - In the register, it may be that between two records have blank lines. For example, between Manoel and Carlos' record, there’s a blank line.

Thank you.

vba filter

  • I suggest you put an example image, to facilitate the understanding of how your data is arranged in the spreadsheet..

  • I don’t quite understand.. you would like to create a macro where you pass a filter and it copies the data from plan2 to plan1?

  • I edited the question by putting an image of what the data would look like. In case I don’t know how many records you have in Sheet2 and can also contain blank lines between records.

2 answers

0

To add to the question, how others may have the same problem. I would approach it differently. First I would go to the second tab and make a loop independent of the number of lines and the presence of blank lines, as already answered by the author, but without using the filter.

Sub copyFromSheet2()

Dim lngUltimaLinha As Long
Dim i As Integer
Dim strNomeCidade As String

lngUltimaLinha = ActiveWorkbook.Sheets("Sheet2").Range("B1048576").End(xlUp).Row

'Aqui vai o nome da cidade que deseja, pode ser por InputBox também
strNomeCidade = "c1"

'Loop para salvar as linhas na outra planilha, no caso, Sheet1
'Se a planilha Sheet1 estiver em branco, a primeira linha vai ficar em branco
For i = 1 To lngUltimaLinha
    If Cells(i, 2).Value = strNomeCidade Then
    Cells(i, 2).EntireRow.Copy ActiveWorkbook.Sheets("Sheet1").Range("A1048576").End(xlUp).Offset(1, 0)
    End If
Next i

End Sub

0


I got it here, guys.

Discovering the last filled line, with the command below, independent of the blank lines and the line quantity worked:

UltimaLinha = PlanilhaOrigem.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

And for the filter I used the commands below:

PlanilhaOrigem.Sheets("Sheet1").Range(LocalOrigem).Select    
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="<300"    
Range(LocalOrigem).CurrentRegion.Copy

Browser other questions tagged vba filter

You are not signed in. Login or sign up in order to post.