Select & Move Range

[Gene Augustin]

I need a macro to select from the first non-blank cell (containing data) to
the last non-blank cell in a row containing data. There are blank cells
between the first and last cell containing data.

I then want to move that selected range (not the data) to another row.
Something like:

FirstCell = first non-blank cell
LastCell = last non blank cell
Range = (firstcell,lastcell)
Range.Offset(-1,0)

Gene

 

[Don Guillett]

One way. However, selections are seldom desirable. Post all of your code.

Sub findfirstlastcell()
mc = 4 '"d"
fr = Cells(2, mc).End(xlDown).Row
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select
'or
'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select
End Sub

 

[Gene Augustin]

At first, I got error messages. I added Dim statements for the variables and
I runs, but doesn't give correct results.
Here are the contents of A1:A12

CELL CONTENTS
B1 Data
B2 1
B3
B4 2
B5
B6
B7 3
B8 4
B9 5
B10 6
B11 7
B12 8

I select the cell below The cell containing "Data" and run the macro.

I want the macro to select the 11 cell range B2:B12 and move the selection
range to A2:A12.

The macro produces a selection of 9 cells beginning in row 4 of the adjacent
column.
It should produce the selection the 11 cell range A2:A12.

Both versions of the Range(CellsŠ line produce the same results.

I mis-stated the problem in my original request. I want the range from the
second cell in the column to the last non-blank cell in the colum.

Here's your modified code with the DIM statements added:

Sub findfirstlastcell()
Dim mc As Long
Dim fr As Long
Dim lr As Long
mc = 2 ' "d"
fr = Cells(mc, 2).End(xlDown).Row
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select
'or
'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select

End Sub

 

[Gene Augustin]

At first, I got error messages. I added Dim statements for the variables and I
runs, but doesn't give correct results.
Here are the contents of A1:A12

CELL CONTENTS
B1 Data
B2 1
B3
B4 2
B5
B6
B7 3
B8 4
B9 5
B10 6
B11 7
B12 8

I select the cell below The cell containing "Data" and run the macro.

I want the macro to select the 11 cell range B2:B12 and move the selection
range to A2:A12.

The macro produces a selection of 9 cells beginning in row 4 of the adjacent
column.
It should produce the selection the 11 cell range A2:A12.

Both versions of the Range(CellsŠ line produce the same results.

I mis-stated the problem in my original request. I want the range from the
second cell in the column to the last non-blank cell in the colum.

Here's your modified code with the DIM statements added:

Sub findfirstlastcell()
Dim mc As Long
Dim fr As Long
Dim lr As Long
mc = 2 ' "d"
fr = Cells(mc, 2).End(xlDown).Row
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select
'or
'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select

End Sub









Problem solved. I redefined fr and it works now.
fr = Cells(2, 2).Row

I don¹t understand what is happening with statement Cells(2,2)

Can you explain?

 

[Don Guillett]

Re: Select & Move RangeYou misunderstand how cells works. I repeat that selections are seldom desired.
cells(ROW,COLUMN)
fr = Cells(mc, 2).End(xlDown).Row

Except for the dim statements which you didn't mention option explicit, my ORIGINAL works just fine for selecting the second cell, assuming a header row and a value in the 2nd cell by ONE simple change: cells(1,mc) instead of cells(2,mc). If, in fact, you always want row 2 then just get rid of the fr part and use
Range(Cells(2, mc), Cells(lr, mc)).Offset(, -1).Select

Sub findfirstlastcell()
Dim mc, fr, lr As Long
mc = 2 '"d"
fr = Cells(1, mc).End(xlDown).Row
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
At first, I got error messages. I added Dim statements for the variables and I runs, but doesn't give correct results.
Here are the contents of A1:A12

CELL CONTENTS
B1 Data
B2 1
B3
B4 2
B5
B6
B7 3
B8 4
B9 5
B10 6
B11 7
B12 8

I select the cell below The cell containing "Data" and run the macro.

I want the macro to select the 11 cell range B2:B12 and move the selection range to A2:A12.

The macro produces a selection of 9 cells beginning in row 4 of the adjacent column.
It should produce the selection the 11 cell range A2:A12.

Both versions of the Range(Cells. line produce the same results.

I mis-stated the problem in my original request. I want the range from the second cell in the column to the last non-blank cell in the colum.

Here's your modified code with the DIM statements added:

Sub findfirstlastcell()
Dim mc As Long
Dim fr As Long
Dim lr As Long
mc = 2 ' "d"
fr = Cells(mc, 2).End(xlDown).Row
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select
'or
'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select

End Sub