VBA replace everything in sheet except first rows

Question

I have a working VBA script which currently replaces all dots by commas (posted below). However I would the script avoid the first two rows and change everything else. What would be the best and easiest way to do this? I have only found solutions where you have to set a range and but I want it to work for all excel sheets regardless of the number of rows or columns.

Sheets("Sheet1").Select
Cells.Replace what:=".", Replacement:=",", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

If you don't want to set the range you could use `Cells.Resize(Rows.Count - 2).Offset(2).Replace...` — Darren Bartrup-Cook, Nov 29 '18 at 16:06
[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Nov 29 '18 at 16:20
`With ActiveSheet.UsedRange : .Offset(2).Replace.... : End With` — JohnyL, Nov 29 '18 at 16:54

score 0 · Answer 1 · answered Nov 29 '18 at 16:30

0

You can still use range, but generate it dynamically: use the macro recorder and see what it gives you if you select all cells with the Select All button at the top left corner, then hold down CTRL and unselect by clicking the row headers.
After having a selected range, you can use Application.Selection instead of Cells.

answered Nov 29 '18 at 16:30

Dávid Laczkó

1,091
2
6
25

As Peh said in his comment. Much better to avoid `Select`, `Selection`, `Active` – Darren Bartrup-Cook Nov 29 '18 at 16:37
@DarrenBartrup-Cook this is absolutely just a starter, the generated code needs to be changed as necessary, it is a solution that does not start from scratch - also not optimal. – Dávid Laczkó Nov 29 '18 at 17:04

score 0 · Answer 2 · answered Nov 29 '18 at 16:42

0

set wsCSV = ... (your csv worksheet)
set rng = wsCSV.UsedRange
set ws = ... (your worksheet)
ws.UsedRange.Offset(1).ClearContents()
ws.Range("A2").Resize(rng.Rows.Count, rng.Columns.Count).Values = rng.Values

answered Nov 29 '18 at 16:42

Alpha

2,372
3
21
23

VBA replace everything in sheet except first rows

2 Answers2