How to get information from Excel cells using VB

Question

I’m trying to get information from a column in Excel using VB. Let’s assume that I have 5 lines and only 2 of them are filled with some information and the other 3 are null.

I need a code that takes only these two lines and when it reaches the third and sees that it is null stop the program and pass me only the two information that are filled.

I got it with a code, but I determined it from the line A1 until A5 for example for him to get the information and if he has more he will not do the search in A6 for example. I want to optimize this and he alone knows he has more and just take the lines that are filled and forget the nulls.

Code I tried; I determined the line that he took the information and he will not do the automatic search if there is a line B29.

 Dim Cel, A1, A2
 A1 = "B23"
 A2 = "B28"
 Cel = GetCells(A1&":"&A2)

Answer 1

You can make a loop for which reads the value of the variable, stores and skips a line down; if the next line is empty it stops and exits the loop, otherwise it continues.

In the example below I did it in a more explained way, with auxiliary variable x, but you can do straight through a more concise code.

Sub exemplo()

Dim x, NumRows, temp As Integer

Range("A1").Select 'Seta o ponto inicial da sua verificação
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count 'Pega o total de linhas com valor e salva em uma variável

For x = 1 To NumRows

 temp = ActiveCell.Value 'Aqui vc pega o valor da célula atual (começa em A1 e vai incrementando a cada laço)
 ActiveCell.Offset(1, 0).Select 'Pula para a linha de baixo
 Next x

End Sub