How to simplify vba code?

[ChanL]

Hi, currently I'm working on a VBA code that I have to perform multiple task in a sheet namely "Sheet1".
I have wrote a code on it, but I feel like it is to length and need a way to refine it.
This is my code:

Sub formatsheet()

'removedot
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace what:=".", replacement:=" ", lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, ReplaceFormat:=False, formulaversion:=xlReplaceFormula2

'insert I
Sheets("Sheet1").Select
Sheets("Sheet1").Range("B2").Select
Selection.Value = "I"
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

'Fill country
Sheets("Sheet1").Select
Sheets("Sheet1").Range("G2").Value = "USA"
Range("G2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

End Sub

The main reason why I keep repeating "Sheets("Sheet1").select" is because before this code, I actually have another code that need it to copy data from other sheets by refer to its header column. And when I didn't add "Sheets("Sheet1").select", it keep runs error.

 

[ChanL]

I think i figure out the problem, there is a typo when I type my code. So sorry. And just a quick question, I just curious, for code like this, it is always better we use different destination ? For example, in the range part , you use F2, while at the destination part you use H2. I tried using F2 for both and it went well.

Just ask this for my own learning and understanding!
Also thanks so much for your help, you have made my day!

Did you try it using Range("F2")....I simply thought that was an error >>

 

[Michael M]

Glad it worked for you.
To answer your question, you only need to use the destination where you want the text to columns to start from.
If F2 is the correct location then that's fine.

 

[ChanL]

MAybe this, but the other code may conflict without seeing it !!

Sub formatsheet()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows, Count, "A").End(xlUp).Row
With Sheets("Sheet1")
    .Range("A2:A" & lr).Replace what:=".", replacement:=" "
    .Range("B2:B" & lr).Value = "I"
    .Range("G2:G" & lr).Value = "USA"
End With
End Sub

After i double check, i realize the replace code line does not work, it does not take away the "." like I want.

Then I change the code for the replacement part to something like this:

.range("A2:A" & lr).texttocolumns destination:=range("A2"),Datatype:= xldelimited,textqualifier:=xlDoubleQuote, _
other:=True, OtherChar:=".", fieldinfo:=array(1,4), trailingminusnumbers=true

its work ok, but just it will create a new column next to column A which makes me need to write a simple code to delete it afterwards.
Just wondering why .Range("A2:A" & lr).Replace what:=".", replacement:=" " won't work

 

[Michael M]

There is a typo in the LR line, try this !

Sub formatsheet()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet1")
    .Range("A2:A" & lr).Replace what:=".", replacement:=" "
    .Range("B2:B" & lr).Value = "I"
    .Range("G2:G" & lr).Value = "USA"
End With
End Sub