Getting a range data of a column from Excel 2016 through VBA

[LeilaMansouri]

Hi,

I am trying to get a range of data from a column of my Excel sheet on VBA, and then process each cell of the column one by one.

By there is a run time error "Subscript out of range" while getting the ListObject.

Could you please let me know what this wrong with the code and who can I resolve it?

Dim sourceWS As WorksheetDim databaseTable As ListObject
Dim pinNumberColumn As Range
    
Set sourceWS = Sheets("Hello")
Set databaseTable = sourceWS.ListObjects(1)

Set pinNumberColumn = databaseTable.ListColumns("Pin-Nr.").DataBodyRange

 

[MrJoosten]

Hello Leila,

I use the following code for looping through the cells in column A. It puts "Hello" in the used range.

Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row 
For i = lr To 1 Step -1
cells(I,1).Value = "hello" 'adjust the range to your needs. 
Next

Hope this helps.

 

[LeilaMansouri]

Hello Leila,

I use the following code for looping through the cells in column A. It puts "Hello" in the used range.

Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row 
For i = lr To 1 Step -1
cells(I,1).Value = "hello" 'adjust the range to your needs. 
Next

Hope this helps.

Hi [FONT=&quot]MrJoosten,

[/FONT]Thanks for the response. "hello" is the name of excel sheet used to read data from. Also I would like to read data of a column by the name of its header. (here "Pin-Nr.")

How could I specify which excel sheet it should read from? and how to read a range of column data just by the header name?

Thanks.