CODEWITHSUNDEEP
excelvba
kmfdm
kmfdm

Reputation: 79

Removing column(s) from selected range

I have the following code that selects a range based on the rows that have data in the sheet, starting with row 10:

Range("B10").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select

this works well, but by default it captures all columns with data. I'm not sure how to then change it to only capture specific columns.

for example, if I only wanted columns B:D for rows with data selected, changing it to

Range("B10:D10").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select

does not work, because the *SpecialCell*s selection overrides that and still returns all columns with data.

Is there an easy way to drop column(s) off of the currently selected range after SpecialCells captures the range?

Upvotes: 0

Views: 983

Answers (1)

nutsch
nutsch

Reputation: 5962

I'd use xlup to get the last row filled, as in 

debug.print range("B10:D" & cells(rows.count,"D").end(xlup).row).address

I'd also refrain from using select unless I want to return a selection to the end user.

Upvotes: 1

Related Questions