EXCEL VBA Question

Posted By: Anonymous

EXCEL VBA Question - 26/06/2018 12:16

Hi all,

I have data that I need to format using conditional formatting as part of an automated process.

I need to select all data in one column, but there are gaps in the data.

I have tried using the following code, but it just selects the entire column. I only want to go to the last row with data in it if that makes sense?

Current VBA code:
Sub trest()
Dim DataRange As Range

Set DataRange = Range("I1:I" & Cells.SpecialCells(xlCellTypeLastCell).Row)
DataRange.Select
End Sub

Anyone know how to correct this code?

Posted By: Nigel

Re: EXCEL VBA Question - 26/06/2018 12:26

Is the last row with data in it variable? (ie does the number of rows increase / decrease?)

Posted By: Anonymous

Re: EXCEL VBA Question - 26/06/2018 12:27

Yes and that is the issue.

Posted By: Nigel

Re: EXCEL VBA Question - 26/06/2018 12:34

try this (only lifted from t'internet - not my own work)

Originally Posted By Some VBA from the intenet

Sub Select_Column()
Dim ac As Integer
Dim lastr As Long
Dim sh As Worksheet

Set sh = ActiveSheet
ac = ActiveCell.Column
lastr = sh.Cells(Rows.Count, ac).End(xlUp).Row
Range(Cells(2, ac), Cells(lastr, ac)).Select
End Sub