How can I make this code better?

[G12]

Hello.

As you can see from the number of posts, I am new.

I have however been lurking around here for some time and want to say thank you to all the regulars who provide answers to both easy (for them, hard for me) and difficult questions. If you see any of your bits of code in the following, I tip my hat to you. Thank you very much.

I have managed to find the majority of what I need by using the search function. In general someone has asked a question and received a reply which almost fits my needs. I have also done my best to use the help function (mostly not that useful as I do not really have a great understanding of what the standard terms mean) and to attempt to get the answers by a bit of trial and error.

So. Firstly, can someone give me a reading suggestion of a guide book which will explain in laymen's terms what the standard terms (such as array and the more complicated ones!) mean. I think this will be imperative if I am to improve. Understanding the "Help" file will......help.

Secondly, would someone be kind enough to look through the following code to explain how it could have been better achieved and more importantly, why that would have been the case.

The code is fairly straight forward re-formatting of a SAP download sheet. All I am doing is moving the information around on the sheet to allow easier access and manipulation of the data. This was also my first attempt with ranges and thanks to user who added the macro code ManipulateSomeCells(). As the number of rows will be different in every dowload it seemed a smarter way to do it. Also the recently answered message box questions worked a treat so thanks to you.

Thanks again to all the regular posters for all their advice to everyone. If I have missed key information required for you to answer my questions, please let me know.

Cheers
G12


Sub PricePageCleaner()
'
'

' Confirm use price clean
rspn = MsgBox("Do you want to run the Page Cleaner?", vbYesNo)
If rspn = vbNo Then Exit Sub
On Error Resume Next

' Open Data Sheet and Insert Columns
Range("A1").Select
Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

' Add Titles
Range("A7").Select
ActiveCell.FormulaR1C1 = "Heading 1"
Range("B7").Select
ActiveCell.FormulaR1C1 = "Heading 2"
Range("C7").Select
ActiveCell.FormulaR1C1 = "Heading 3"
Range("N7").Select
ActiveCell.FormulaR1C1 = "Heading 4"

Range("G7").Select
ActiveCell.FormulaR1C1 = "Heading 5"
Range("J7").Select
ActiveCell.FormulaR1C1 = "Heading 6"
Range("K7").Select
ActiveCell.FormulaR1C1 = "Heading 7"
Range("L7").Select
ActiveCell.FormulaR1C1 = "Heading 8"
Range("M7").Select
ActiveCell.FormulaR1C1 = "Heading 9"

Range("A10").Select
ActiveCell.FormulaR1C1 = "=LEFT(R1C6,4)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[2]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[3]"
Range("N10").Select
ActiveCell.FormulaR1C1 = "=LEFT(R2C6,3)"

' Auto Fill LHS Cell Range

Range("A10").Select

Dim rngLHS As Range

ActiveCell.SpecialCells(xlLastCell).Select
Cells(ActiveCell.SpecialCells(xlLastCell).Row, "A").Select

Set rngLHS = Range(Selection, Selection.End(xlUp).Offset(0, 2))

Range("A10:C12").Select
Selection.Copy

Selection.AutoFill Destination:=rngLHS, Type:=xlFillDefault


' Auto Fill RHS Cell Range
Range("N10").Select

ActiveCell.SpecialCells(xlLastCell).Select

Dim rngRHS As Range


Set rngRHS = Range(Selection, Selection.End(xlUp))

Range("N10:N12").Select
Selection.Copy

Selection.AutoFill Destination:=rngRHS, Type:=xlFillDefault

' Convert All To Values
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Delete Blank Rows

Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="="
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp


' Delete Blank Column
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft

' Format Sheet

Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("A:N").Select
Columns("A:N").EntireColumn.AutoFit
Cells.Select
Selection.RowHeight = 15.75

' Export Details
' None added at present


End Sub

 

[Weaver]

Range("A7").Select
ActiveCell.FormulaR1C1 = "Heading 1"

etc can always be replaced with

Range("A7").value = "Heading 1"

as you don't need to select a cell in vba to put something in it.

You could replace this whole chunk with

for x = 1 to 9
cells(7,x).value="Heading " & x
next x

This may give you some ideas of your own. I learned just about everything I know about vba via recording macros and then tinkering, heck I still do!