All geek questions in one place

Select a column from 1 to 10 rows of ActiveCell in Excel

this is a macro in excel.

While the key combination is pressed (i.e., the macro runs), I need to make some format changes in columns 1 to 10 of the row that has ActiveCell.

I am currently selecting the entire row

ActiveCell.EntireRow.Select

However, I need to select only lines 1 through 10. I think it should be something like

 ActiveCell.Range(1, 10).Select

But that does not work.

To be clear, I read about

 ActiveCell.Offset(5, -4).Select

But this will not work in my case. ActiveCell can be any column in a row, and therefore, the corrected offset will not help.

So, I have great gurus, I hope this is quick material, just somehow I can’t find the answer. Please help.

+4
source share
4 answers

If these are always columns 1 through 10 (i.e., from A to J), then this should work:

 Range("A" & ActiveCell.Row & ":J" & ActiveCell.Row)

For example, if activecell is set to M14 , then it will select the range A14:J14 . Then you can format it as you like.

Hope this helps

+13
source

Ok This is what I did and it works.

 ActiveSheet.Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 10)).Select

In my opinion, this is a kind of hacker way. If there is anything better, answer. Until we get something better, I think this is the best answer for people who come to look for anwer, after me.

+1
source

It will work

 Range("A" & ActiveCell.Row).Resize(1,10).select
0
source

if applicable .. spent a lot of time to find, we need a more programmable - automatic answer, for the results - work on 1 line.

  Dim N7 As String: N7 = RANGE("N7") 'workcell N7 shows eg: A:J Intersect(Rows(ActiveCell.row), RANGE(N7).Columns).Select 'YES ANSWER 'other: If application.Max(Intersect(Rows(ActiveCell.row), RANGE(N7).Columns)) > 0 Then MsgBox "YES" & Space(10), vbQuestion ', "title": end if & vbCr & Else: MsgBox "NO" & Space(10), vbQuestion: End If ', "title": end if & vbCr & 'Cells(ActiveCell.row, J6).OFFSET(, 1).RESIZE(, 6).Select 'YES: offset works on immediate cols for 1 row 'Cells(ActiveCell.row, J6).RESIZE(, 5).Select 'yes on 4 immediate cols for 1 row (row, col) 'Cells(ActiveCell.row, B5).select

work cell N7 has: = SUBSTITUTE (SUBSTITUTE (CELL ("address", $ A7), "$", "), LINE ()," ") &": "SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (CELL (" address ", $ J7), "$", ""), ROW (), "")

0
source

Source: https://habr.com/ru/post/1479317/

More articles:


All Articles