Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Macros in Excel Spread sheets?
I want to write a macro where I can insert a line below the current line, format the cells to merge, bring the text to the top, and wrap the text. I know how to write the macro, however, how do I get the macro to work on any line in the workbook, not just the line I created it on?
Keyboard Shortcut: Ctrl+m
'
Rows("4:4").Select
Selection.Insert Shift:=xlDown
Range("B:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.mergecells = False
How should I change it to make it work? What exactly do I need to type in?
I don't want the rows to be restricted to "4:4", just wherever I hit the command.
I figured out how to get it to restrict to only the columns I want, but I can't figure out how to get it to stay in the row that the unmerged, unwrapped cell started in.
2 Answers
- Anonymous1 decade agoFavorite Answer
Don't use absolute cell addresses. (Record a macro while you do what you want, and look at the code that gets saved.)
Something like this may help you:
Dim i
For i = 147 To 1 Step -2
Rows(i).Select
Selection.Insert <whatever>
Next i
If you just want it to work on one row, select the row first, then run the macro, written to have its effect on the selection.
- Anonymous5 years ago
You don't even need a macro. Format the cells as custom and then you can have three different formats, e.g. #,##0.00;[Red]-#,##0.00; This tells it to format with 2 decimal places for all values, negative values in red, and zeroes left blank.