Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,058
Default 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?

  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,124
Default 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?


  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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?





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,124
Default 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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
newbie - how to paste into an occupied cell Vic Baron New Users to Excel 5 June 5th 08 07:52 PM
Selecting occupied cell over empty ones Rich Excel Discussion (Misc queries) 0 March 28th 07 02:53 AM
Selecting occupied cell over empty ones Teethless mama Excel Discussion (Misc queries) 1 March 28th 07 02:13 AM
subtract from numeric cell when alpha cell is occupied Keith Excel Worksheet Functions 0 March 28th 07 01:04 AM
Selecting occupied cell over empty ones Rich Excel Discussion (Misc queries) 0 March 28th 07 12:05 AM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"