Fiat Coupe Forum
- Founded by Kayjey & James Northam
- Funded by the Club for the benefit of all owners
Fiat Coupe Club UK
join the club
Fiat Coupe Forum
 
» Announced
    Posting images


» Related sites
    Main club site
    fiatcoupe.net


» External data
    owners listed
 
Who's Online Now
4 registered members (paul, Cyborgguy, 386ka, slime500), 360 guests, and 2 spiders.
Key: Admin, Global Mod, Mod
Forum Statistics
Forums69
Topics113,543
Posts1,340,442
Members1,785
Most Online731
Jan 14th, 2020
Top Posters(All Time)
barnacle 33,522
stan 32,122
Theresa 23,296
PeteP 21,482
bockers 21,070
JimO 17,917
Nigel 17,366
Edinburgh 16,662
RSS Feeds
Club Events
Club Information
Track Events
Rolling Road/RWYB
Social Events
Non-UK Events
Coupé Related Chat
Coupé Spotting
Coupé News/Press
Buying/Selling Advice
Insuring a Coupé
Basic FAQ's
How to Guides
Forum Issues
Technical Problems
General Maintenance
Styling
Tuning
Handling
ICE and Alarm
Coupés for Sale
Coupés Wanted
Parts for Sale
Parts Wanted
Group Buys
Business Forum
Other Vehicles for Sale/Wanted
Other Items for Sale/Wanted
Haggling/Offers
Ebay links
Other Cars
Other Websites
General Chat
Previous Thread
Next Thread
Print Thread
EXCEL VBA Question #1622338
26/06/2018 12:16
26/06/2018 12:16

E
elder81
Unregistered
elder81
Unregistered
E



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?

Re: EXCEL VBA Question [Re: ] #1622339
26/06/2018 12:26
26/06/2018 12:26
Joined: Dec 2005
Posts: 17,366
Staffordshire
Nigel Offline
Forum veteran
Nigel  Offline
Forum veteran

Joined: Dec 2005
Posts: 17,366
Staffordshire
Is the last row with data in it variable? (ie does the number of rows increase / decrease?)


[Linked Image]
Re: EXCEL VBA Question [Re: ] #1622340
26/06/2018 12:27
26/06/2018 12:27

E
elder81
Unregistered
elder81
Unregistered
E



Yes and that is the issue.

Re: EXCEL VBA Question [Re: ] #1622341
26/06/2018 12:34
26/06/2018 12:34
Joined: Dec 2005
Posts: 17,366
Staffordshire
Nigel Offline
Forum veteran
Nigel  Offline
Forum veteran

Joined: Dec 2005
Posts: 17,366
Staffordshire
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


[Linked Image]

Powered by UBB.threads™ PHP Forum Software 7.7.1
(Release build 20190129)
PHP: 7.3.33 Page Time: 0.009s Queries: 15 (0.004s) Memory: 0.7515 MB (Peak: 0.7970 MB) Data Comp: Off Server Time: 2024-03-29 15:35:55 UTC