CODEWITHSUNDEEP
vbaexcel
Mikkel Astrup
Mikkel Astrup

Reputation: 415

Remove rows from selectrange VBA

Im trying to write a VBA to copy and paste alot of different data, there is a lot of non-important data in each sheet so i want to copy only the important part. The VBA i have now looks like this: 

Sub DynamicRange()
'Best used when your data does not have any entirely blank rows or columns

Dim sht As Worksheet
Dim StartCell As Range

Set sht = Worksheets("Kvalitetskriterier 2015")
Set StartCell = Range("A8")

'Select Range
  StartCell.CurrentRegion.Select

End Sub

What I need is a way to, from the seleced range, remove the first row and the last 4 rows as i dont need that part. Is there a way to do that from my VBA?

Upvotes: 1

Views: 2281

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

There's no need to Select the range if you want to copy it, you can directly Copy it.

if you want to get rid of the first row of the Range, you can use Range.Ofset(1, 0), this will remove the first row (Header).

If you also want to get rid of rows at the end of your Range, you can use Range.Resize(- number of rows you want to remove).

Code

Sub DynamicRange()
'Best used when your data does not have any entirely blank rows or columns

Dim sht As Worksheet
Dim StartCell As Range

Set sht = Worksheets("Kvalitetskriterier 2015")
Set StartCell = sht.Range("A8")

'Select current region
Set StartCell = StartCell.CurrentRegion

' set offeset 1 row (remove header), and use Resize to remove the last row
Set StartCell = StartCell.Offset(1, 0).Resize(StartCell.rows.Count - 2)

StartCell.Copy ' <-- No need to Select it if you want to copy

End Sub

Upvotes: 6

Related Questions