How to simplify vba code?

ChanL

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
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:
VBA 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

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
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 >> :mad:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,876
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 8, 2021
Messages
45
Office Version
  1. 2019
Platform
  1. Windows
MAybe this, but the other code may conflict without seeing it !!
VBA Code:
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:
VBA Code:
.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

Well-known Member
Joined
Oct 27, 2005
Messages
20,876
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
There is a typo in the LR line, try this !
VBA Code:
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
 
Solution

Forum statistics

Threads
1,140,928
Messages
5,703,218
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top