How do you set just a general macro on your keyboard?

Macro to check for all rows with data when apply copy and trim functions

• QUESTION: I've discovered the record macro function today. It's quite a useful feature. I'm trying to clean up a set of data and part of the clean up involves removing the trailing spaces in the data by using the TRIM function. I add a new column B, use TRIM(), then copy and paste special to column A, and delete column B. I created the macro by using the record macro and got the following code. My current data set has 1448 entries. How can I modify this code so it will apply the steps to all the entries in a new set of data and not just the first 1448 rows. I could make the number a high number, such as 3000 but there must be a way to make it so it checks for all rows with data. Sub Clean_Up_data() ' ' Keyboard Shortcut: Ctrl+q ' Columns("A:Q").Select Selection.ColumnWidth = 17 Columns("B:B").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("B2").Select ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])" Range("B2").Select Selection.AutoFill Destination:=Range("B2:B1448") Range("B2:B1448").Select Selection.Copy Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  :=False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ANSWER: You would need to find the last used row in column A. This can be done with cells(rows.count,"A").end(xlup).row I have shown you how you might implement that in your code by storing the value in a variable (arbitrarily named "lastrow") and then using that to replace the hard code row number in two places. Sub Clean_Up_data() ' ' Keyboard Shortcut: Ctrl+q ' Dim lastrow as Long lastrow = cells(rows.count,"A").End(xlup).row Columns("A:Q").Select Selection.ColumnWidth = 17 Columns("B:B").Select Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Range("B2").Select ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])" Range("B2").Select Selection.AutoFill Destination:=Range("B2:B" & lastrow ) Range("B2:B" & lastrow).Select Selection.Copy Range("A2").Select Selection.PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft end Sub as you progress in your knowledge of VBA you will discover that the select -> selection paradigm recorded by the macro recorder can often be dispensed with. for example this sequenct Range("B2").Select ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])" Range("B2").Select Selection.AutoFill Destination:=Range("B2:B" & lastrow ) Range("B2:B" & lastrow).Select Selection.Copy Range("A2").Select Selection.PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False could be done with Range("B2").FormulaR1C1 = "=TRIM(RC[-1])" Range("B2").AutoFill Destination:=Range("B2:B" & lastrow ) Range("B2:B" & lastrow).copy Range("A2").PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False ---------- FOLLOW-UP ---------- QUESTION: Your solution worked. Thank you. What do you mean by "as you progress in your knowledge of VBA you will discover that the select -> selection paradigm recorded by the macro recorder can often be dispensed with" Are you saying that there are ways to simplify the code that the Recod Macro feature outputs?

• Answer:

  Yes - that is what I meant - I then provided an example from a segment of your code. Take a look at this site http://www.cpearson.com/Excel/MainPage.aspx select one of the links that involves VBA code or go to the topic index and then go to any link to a page that involves VBA. You will notice there are generally no commands that would relate to navigation such a selecting and activating. The recorder records such things because that is what you are physically doing, but you can reference sheets and cells and other elements of theobject model without selecting or activating.