Querying Data With VBA Filter

Navigation
0

Good evening, good evening

I have a problem with VBA, can you help me, please. Following:

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

InVBA,IneedtocopythedatafromSheet2andpasteitontoSheet1basedonsomefilters,forexamplewheretheCityisequalto"São Paulo".

Example of the result would be on Sheet1, copy the data where the city is equal to São Paulo, then would have John and Manuel.

Some points that can be a hindrance: 1 - I do not know the number of rows in the register of Sheet2. For example, as the image above, the register has 4 records, but could have 200. In this case I do not know in advance how many lines it has.

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

Thank you.

    
asked by anonymous 31.05.2017 / 06:21

2 answers

0

I got some people here.

Finding the last line filled, with the command below, regardless of the blank lines and the amount of line worked: (Rows.Count, 1) .End (xlUp) .Row

And for the filter I used the commands below: Spreadsheet.Sheets ("Sheet1"). Range (LocalOrigem) .Select Selection.AutoFilter Selection.AutoFilter Field: = 3, Criteria1:="< 300"
Range (LocalOrigem) .CurrentRegion.Copy

    
01.06.2017 / 15:19
0

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

Sub copyFromSheet2()

Dim lngLastRow 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
    
25.08.2017 / 18:48
insert data from an array into the database - java SearchView with no return on Android