Coding to choose the previous completed cell in a column

[Darren Ingram]

Hi,

I have used the Macro recorder to create a macro for me however when I apply
the macro to other similar worksheets in bombs out. What I have recorded is
for the macro in whatever column to go back to column A in the row and then
go up to the previously filled cell in column A and complete a set function.

What the recorder records is a change in the number of cells (0, -15) or
whatever and it then applies that movement change to all future runnings.

Coding is as follows ( I hope this helps)

ActiveCell.Offset(0, 1).Range("A1:I1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
ActiveCell.Offset(0, 9).Range("A1:B1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
ActiveCell.Offset(0, 5).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
ActiveCell.Offset(0, -15).Range("A1").Select
Selection.Cut
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.ClearContents
End Sub

I don't want it to recorded the number of times I've moved up...just the
fact that I've moved up. When I recorded it I use the Ctrl+(up arrow) to
record the movement however it has given this a literal value of -15.

I am trying to move around the worksheet using Ctrl+ and each time is does
the above. I'm also selecting cells in a similar way by selecting the cell
in a column (That is blank) and doing a Shift+Ctrl+up arrow and doing a
Ctrl+D to fill the calculations held in the first cell.

This also doesn't work as it's taking literal cell movements.

I hope that someone is able to help me

 

[Bob Phillips]

Cells(ActiveCell.Row, "A").End(xlUp).Select

 

[Don Guillett]

I'm not quite sure of all you want to do but maybe this will get you
started. However, Selections are NOT necessary or desirable.

Sub gotocolacell()
Cells(ActiveCell.Row, "a").End(xlUp).Select
End Sub

Sub workoncell()
Cells(ActiveCell.Row, "a").End(xlUp).copy
End Sub
'or
Sub gotocolacell()
With Cells(ActiveCell.Row, "a").End(xlUp)
.Borders.LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlContinuous

End With
End Sub

 

[Darren Ingram]

Don,

I have to confess that I'm not sure of what is going on...

It seems that the code you wrote sends me to cell A1 all the time. (It did
when I played around with it)

However I only want it to go up the rows in a column (any column) to the
previous entered cell either selecting all cells in between (and then doing a
Ctrl+D) or I just bounce from the cell I'm currently in up to the previous
filled in cell (not selecting the inbetween cells)..... ie. J10:J5
(selecting) or from J10 to J5 (assuming there is no data in J6,J7,J8,J9).

I know that the answer lies in the "a" par of the following but I'm not
sure of its relationship here. ****ActiveCell.Row, "a").End(xlUp*****

I'm very new at this so please bear with me.

Regards,

 

[Bob Phillips]

Did you try mine?

 

[Darren Ingram]

Hi Bob,

I have just tried yours....but I think I have done something wrong (due to
my inexperience with coding and general newbieness)

I put your line of code into:

Sub Part1()
'
' Part1 Macro
' Draw line under row from column B to column J
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
ActiveCell.Offset(0, 1).Range("A1:I1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
ActiveCell.Offset(0, 9).Range("A1:B1").Select
‘
‘Range(Selection, Selection.End(xlUp)).Select
‘
Cells(ActiveCell.Row, "A").End(xlUp).Select
Selection.FillDown
ActiveCell.Offset(0, 5).Range("A1").Select
‘
‘Range(Selection, Selection.End(xlUp)).Select
‘
Cells(ActiveCell.Row, "A").End(xlUp).Select
Selection.FillDown
ActiveCell.Offset(0, -15).Range("A1").Select
Selection.Cut
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.ClearContents
End Sub


where my original code is left in but I have put ( ' ) in front so that it
doesn't affect the script but I can see what I originally had for learning
purposes. I put your line of code below it.... Was this right?

It obviously wasn't because when I ran it I receive an error. Your thoughts?

Thank you for your patience.... I'm really keen to learn macros as they
would play such a benefical role in my day to day work activities.

Regards,