Can this work in a Macro

[Mortadella]

Sub Macro1()

'

' Macro1 Macro

' This is a great function on a spreadsheet but in a macro

' it wants to go all the way down to cell 65536 which sort of

' negates the whole point of a macro’s speed

Range("A4").Select

Selection.End(xlDown).Select

Range("B5:B338").Select

Range("B338").Activate

Selection.Copy

Range("G1").Select

ActiveSheet.Paste

Selection.SpecialCells(xlCellTypeBlanks).Select

Application.CutCopyMode = False

Selection.FormulaR1C1 = "=R[-1]C"

ActiveWindow.SmallScroll Down:=309

Range("G1:G334").Select

Range("G334").Activate

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

 

[Hui]

Mortadella


This macro copies B5:B338 and pastes it at G1


It could have been simply done with a single line

Range("B5:B338").Copy Destination:=Range("G1")

Have I missed something ?

 

[PSG]

Hui off the track, I want to know how do you guys manage a different font as shown above in your answer?


The code you mentioned has different font then rest of the text.

 

[Hui]

@PSG

Put a single ` before and after the text for code

Put a < s trong> Your Text < / strong> for bold

(without the extra spaces)


see at the very bottom of this page for other codes


<strong>Allowed markup: a blockquote code em strong ul ol li.

You can also put code in between backtick ( ` ) characters.

 

[JP]

It has to be a backtick, i.e. grave character, which is usually found next to the number 1 on most keyboards (above the letter keys).

 

[Hui]

Thanx JP,

I've updated my post

 

[Mortadella]

I guess it doesn't show what really happens. I am working on a report that has an account number in column a and then several lines further down it will have another. This goes on for several hundred lines. What I do manually that I would like to get into a macro is the following:


I copy the specific cells I want filled from the original column with the gaps to the first cell in another column. In the new location with all the cells I copied selected I then hit the F5 key and select blanks. All the vacant cells are then highlighted. On the formul bar I enter the formula =R1 (for column R) and then hit Control Enter. When I do this all the empty cells are filled with the account numbers I need. In order to convert the formulas that get put in this way I then do a copy paste special values and put it back in the account column and I then have an identifier on each line.


I've been trying to code the function into a macro but when I do it doesn't stop at the bottom I want it goies all the way to cell R65536 which takes an inordinate amount of time and makes the whole point of a macro seem a waste of time.


Is there any way to make this work in a macro?

 

[Hui]

Can you post your file somewhere

With instructions on what you want done?

 

[Hui]

Can you post your file somewhere

With instructions on what you want done?

 

[Mortadella]

I don't know how to

 

[Hui]

You can read how here

http://chandoo.org/forums/topic/posting-a-sample-workbook

Or click on the Excel Ninja below my picture to the left

My email is at the bottom of the page

 

[Mortadella]

I ended up writing a formula that worked so my problem is solved but if you have a column of names and vacant cells between them that you want filled with the name in the first cell (A1) until the second name appears and then switches to that name until the third name, etc., etc., in Excel you can highlight the range of cells click F5 and select blanks then you enter the formula =A1 and hit Ctrl+Enter and all the vacant cells will be filled with the names you have highlighted. I was trying to incorporate that into a macro but found that instead of finishing at the bottom of the highlighted cells where it does in Excel in the macro it wouldn't stop until it reached the bottom of the spreadsheet. If you know why it does this I would be most interested.