pick up last occupied cell to the left macro
 

I'm trying to clean up .pdf data that was exported to Excel. The columns are
a mess so I'm working left to right. I was hoping to create a macro that
would pick up the last occupied cell to the left. It is not always in the
same column but when I run the macro, it always goes to the same column. Is
there a way to say "+, ctrl left arrow, enter" and have it repeat everytime
hit the hotkey assigned?

Or... Does anyone know a better way to clean up a worksheet?

pick up last occupied cell to the left macro
 

Say in row #5 there is data in A5, C5, and F5. Do you want to go to A5 or F5?
--
Gary''s Student - gsnu200793


"Meanie" wrote:

I'm trying to clean up .pdf data that was exported to Excel. The columns are
a mess so I'm working left to right. I was hoping to create a macro that
would pick up the last occupied cell to the left. It is not always in the
same column but when I run the macro, it always goes to the same column. Is
there a way to say "+, ctrl left arrow, enter" and have it repeat everytime
hit the hotkey assigned?

Or... Does anyone know a better way to clean up a worksheet?

pick up last occupied cell to the left macro
 

using your example, I want to fill J5 with data from F5.

row #6, there is data in A5, C5 AND H5. I want to fill J5 with data from H5.

"Gary''s Student" wrote:

Say in row #5 there is data in A5, C5, and F5. Do you want to go to A5 or F5?
--
Gary''s Student - gsnu200793


"Meanie" wrote:

I'm trying to clean up .pdf data that was exported to Excel. The columns are
a mess so I'm working left to right. I was hoping to create a macro that
would pick up the last occupied cell to the left. It is not always in the
same column but when I run the macro, it always goes to the same column. Is
there a way to say "+, ctrl left arrow, enter" and have it repeat everytime
hit the hotkey assigned?

Or... Does anyone know a better way to clean up a worksheet?

pick up last occupied cell to the left macro
 

This will even determine the column to copy to. If you want col J change
Cells(i, lc).value = Cells(i, Columns.Count).End(xlToLeft).value
to
Cells(i, "j").value = Cells(i, Columns.Count).End(xlToLeft).value


Sub lineemup()
lc = Cells.SpecialCells(xlCellTypeLastCell).Column + 1
lr = Cells.SpecialCells(xlCellTypeLastCell).Row
'MsgBox lc
'MsgBox lr
For i = 1 To lr
Cells(i, lc).value = Cells(i, Columns.Count).End(xlToLeft).value
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Meanie" wrote in message
...
I'm trying to clean up .pdf data that was exported to Excel. The columns
are
a mess so I'm working left to right. I was hoping to create a macro that
would pick up the last occupied cell to the left. It is not always in the
same column but when I run the macro, it always goes to the same column.
Is
there a way to say "+, ctrl left arrow, enter" and have it repeat
everytime
hit the hotkey assigned?

Or... Does anyone know a better way to clean up a worksheet?

pick up last occupied cell to the left macro
 

That worked great Don. Thank you. Now, can it be taken another step to the
second to the last occupied cell and so on as the cleanup dictates?


"Don Guillett" wrote:

This will even determine the column to copy to. If you want col J change
Cells(i, lc).value = Cells(i, Columns.Count).End(xlToLeft).value
to
Cells(i, "j").value = Cells(i, Columns.Count).End(xlToLeft).value


Sub lineemup()
lc = Cells.SpecialCells(xlCellTypeLastCell).Column + 1
lr = Cells.SpecialCells(xlCellTypeLastCell).Row
'MsgBox lc
'MsgBox lr
For i = 1 To lr
Cells(i, lc).value = Cells(i, Columns.Count).End(xlToLeft).value
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Meanie" wrote in message
...
I'm trying to clean up .pdf data that was exported to Excel. The columns
are
a mess so I'm working left to right. I was hoping to create a macro that
would pick up the last occupied cell to the left. It is not always in the
same column but when I run the macro, it always goes to the same column.
Is
there a way to say "+, ctrl left arrow, enter" and have it repeat
everytime
hit the hotkey assigned?

Or... Does anyone know a better way to clean up a worksheet?

pick up last occupied cell to the left macro
 

Perhaps you should tell us completely what your layout is and what you are
trying to do. If all else fails, send your workbook to my address below
along with snippets of these emails and a complete explanation along with
before/after examples of what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Meanie" wrote in message
...
That worked great Don. Thank you. Now, can it be taken another step to
the
second to the last occupied cell and so on as the cleanup dictates?


"Don Guillett" wrote:

This will even determine the column to copy to. If you want col J change
Cells(i, lc).value = Cells(i, Columns.Count).End(xlToLeft).value
to
Cells(i, "j").value = Cells(i, Columns.Count).End(xlToLeft).value


Sub lineemup()
lc = Cells.SpecialCells(xlCellTypeLastCell).Column + 1
lr = Cells.SpecialCells(xlCellTypeLastCell).Row
'MsgBox lc
'MsgBox lr
For i = 1 To lr
Cells(i, lc).value = Cells(i, Columns.Count).End(xlToLeft).value
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Meanie" wrote in message
...
I'm trying to clean up .pdf data that was exported to Excel. The
columns
are
a mess so I'm working left to right. I was hoping to create a macro
that
would pick up the last occupied cell to the left. It is not always in
the
same column but when I run the macro, it always goes to the same
column.
Is
there a way to say "+, ctrl left arrow, enter" and have it repeat
everytime
hit the hotkey assigned?

Or... Does anyone know a better way to clean up a worksheet?