VBA, Selection with xlToRight but want to end at column JK

[Frowan1908]

Hi all,

Hopefully an easy question for you .

I am very new to VBA and I am currently fiddling in an already made document to try and understand what is happening.
It is an Excel sheet where we want to copy data from various sheets into a single sheet.
It starts with searching the input value (manually done in the single sheet) in the various sheets which is the common value (from there the search initiates in that row) and declaring that as ' C'.

One of the commands is to look for non empty cells starting from column G (specified by finding the common value stated above and just adding columns (Cells(c.Row, c.Column + 6).Select)) and declaring that as 'D' (Set d = ActiveCell).
It will then search all the way through to the end with the following:
Cells(d.Row, d.Column).Select
Selection.End(xlToRight).Select
Set d = ActiveCell

I would like the selection to stop at column JK but can't seem to find an 'easy' way to do that.
Should I specify the range (active row - column G - JK) earlier and then refer to that range every time? Or is there a simple addon to the ' Selection.End(xlToRight).Select' line?

Hope that what I wrote made sense, I would gladly clarify things if necessary.

Thanks in advance for looking into this.

KR,
Rowan

 

[Joe4]

Welcome to the Board!

Firstly, you can get rid of a lot of your "Select/Selection" statements. It is usually not necessary to actually select the cells, and doing so will actually slow your code down.

Try something like this:

Dim c as Long
Dim d as Range

'Find last column (returns index number of last column)
c = Cells(d.Row, d.Column).Selection.End(xlToRight).Column

'If past column JK (column 271), cap at 271
If c > 271 Then c = 271

'Assign to range
Set d = Cells(d.Row, c)

 

[Frowan1908]

Thanks for your reply.

I will play around with what you suggested, but as the current macro (consisting of 430 line with estimated 300+ "Select/Selection" statements (it is an file dating from 2008, maybe then it was more common )) is not easy for me to re-write... Is there a simple addon to the current line to set a limit for the xlToRight search?

I am planning to completely rewrite and simplify this (with your answer and explanation in mind), but before that happens I will have to catch up on vba knowledge and allocate way more time on this than I currently have. So these are plans for the (near) future .

 

[Joe4]

I will play around with what you suggested, but as the current macro (consisting of 430 line with estimated 300+ "Select/Selection" statements (it is an file dating from 2008, maybe then it was more common )) is not easy for me to re-write... Is there a simple addon to the current line to set a limit for the xlToRight search?

No, it has never really been good programming to use a lot of "Select/Selection" statements in VBA code. A lot of times, it is the result of using the Macro Recorder to create VBA code, which is VERY literal, and records every single selection. The Macro Recorder Tool is very helpful in getting snippets of code, but the code often should be "cleaned up" afterwards to make it run more efficiently (and will shorten the length of your code too). The Macro Recorder also records things you probably don't need and can be eliminated altogether, like scrolling.

There is no "add-on" to cap the "xlToRight" search. What I posted shows you how to handle that. You simply perform the search and see what number it returns. If the number exceeds 271, you just reset it to 271.

That is not a big deal to do in the code. If you take a look at the actual lines of code (and not all the comments I added for explanation sake and variable declaration), you can see that my final code is the same length as you original code (3 lines), and actually does the column check (which yours does not).