VBA code to delete certain rows (until the end) of a table

[Mr2017]

Hi

I've been asked to write code which deletes everything from row 9 downwards in a table.

The table goes from row 7 (which has the titles) and row 8 has some formulas which should be retained.

I'd written this code, which selects a cell in the table, then highlights the region.

Range("D7").CurrentRegion.select

However, I want to offset the region by two rows, so wrote this:

Range("D7").CurrentRegion.Offset(-2,0).Select

However, it won't let me offset the current region by 2 rows (from row 7 to row 9).

Does anyone know how to do this?

I'd then want to delete the new range.

Thanks in advance.

 

[Joe4]

You are welcome!

Note that Resize does have a column component too. We just left that off because we do not need to resize the columns.
To learn more about Resize, see this: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-resize-property-excel

 

[Mr2017]

Hi Joe4/Fluff

I used the code above to delete the rows in my table.

However, I need to move that code to later in the Sub, so if a user runs the initial code (which opens a folder) and they do NOT select a file, then nothing happens.

To recap - I have a Tracker file which tracks billable hours against clients. When some has downloaded a CSV from a website, the code below goes to the downloads folder and allows them to select a file. If they don't select a file, then nothing should happen (it works up until that point).

If they do select a file, then the code should go back to the Tracker file and delete everything below row 8 in the table, then go back to the file just opened and copy the current region, then go back to the Tracker and paste the data as values in cell A7.

When I tried to move the code to later in the file, I got an unexpected error message.

Also, the code now deletes everything from row 3 instead of row 9 in the table. Do you know I can fix it please? Thanks in advance!

Option Explicit



Sub OpenDownload()

'the code below will open a folder or file path then allow you to choose a file to open.

Dim fd As FileDialog
Dim filewaschosen As Boolean

Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Clear

fd.Filters.Add "Old Excel files", "*.xls"
fd.Filters.Add "New Excel files", "*.xlsx"
fd.Filters.Add "Macro Excel files", "*.xlsm"
fd.Filters.Add "Any Excel files", "*.xl*"
fd.Filters.Add "CSV files", "*.csv"


fd.FilterIndex = 5


fd.AllowMultiSelect = False

fd.InitialFileName = Environ("UserProfile") & "\Downloads"

filewaschosen = fd.Show


fd.Execute




'the code below goes back to the Tracker

Workbooks("Tracker - latest version.xlsm").Activate

'The code below deletes everything below row 8

'rtd below stands for rows to delete

Dim rtd As Range

Set rtd = Range("D7").CurrentRegion
rtd.Offset(2, 0).Resize(rtd.Rows.Count - 2).Select
Selection.Delete

Dim importWB As Workbook
Set importWB = ActiveWorkbook

'The code below goes back to the file you opened

Workbooks("importWB").Activate

'This code then copies the current region in that file

Range("E1").CurrentRegion.Copy

're-activate Tracker


Workbooks("Tracker - latest version.xlsm").Activate


'paste copied Celoxis data into cell A7 as values


Range("A7").PasteSpecial xlPasteValues

End Sub

 

[Fluff]

This will delete everything from row 9 downwards

Sub OpenDownload()

'the code below will open a folder or file path then allow you to choose a file to open.

Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim importWB As Workbook

Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Clear

fd.Filters.Add "Old Excel files", "*.xls"
fd.Filters.Add "New Excel files", "*.xlsx"
fd.Filters.Add "Macro Excel files", "*.xlsm"
fd.Filters.Add "Any Excel files", "*.xl*"
fd.Filters.Add "CSV files", "*.csv"


fd.FilterIndex = 5


fd.AllowMultiSelect = False

fd.InitialFileName = Environ("UserProfile") & "\Desktop"

filewaschosen = fd.Show


fd.Execute
Set importWB = ActiveWorkbook




'the code below goes back to the Tracker

Workbooks("Tracker - latest version.xlsm").Activate

'The code below deletes everything below row 8

'rtd below stands for rows to delete


Range("D9", Range("D" & Rows.Count).End(xlUp)).EntireRow.Delete


'The code below goes back to the file you opened

importWB.Activate

'This code then copies the current region in that file

Range("E1").CurrentRegion.Copy

're-activate Tracker


Workbooks("Tracker - latest version.xlsm").Activate


'paste copied Celoxis data into cell A7 as values


Range("A7").PasteSpecial xlPasteValues

End Sub

But I don't understand why you are deleting row 9 onwards, but copying to row 7

I've also moved the Set importWB further up the code, where you had it would cause problems

 

[Mr2017]

Woo hooo!!!

Thanks ever so much!!

That worked!!

How can I like your post? I normally just respond to say thanks, but this deserves extra recognition!

Good question re deleting everything below row 9 but copying to row 7.

The tracker I have has a table where the titles are in row 7 and formulas are in row 8 (from columns J onwards).

When we update the tracker, we need to delete everything in the table, but retain the formulas.

The new data that we add from the download has the titles in row 7 and only goes up to column I.

Because we copy the whole current region in the new data (from the workbook that we open), it includes the titles, so we just paste it all into the tracker from row 7, over-writing the existing titles, but not the formulas, as the data only goes up to column I.

The formulas in column J onwards then update automatically as the table expands when the new data is pasted in.

Let me know if you want me to clarify it further.

Another way to describe it would be that workbook A is our tracker and workbook B is our latest download.

Workbook A has existing data from last month's report from row 7 up to column I. The subsequent columns have separate titles with formulas in the rows below them.

When we import workbook B, we copy everything in workbook B, which includes the column titles that are in row 7 in workbook A, then we just paste it into workbook A from row 7.

Thanks again for your help! You've made my Friday!!!

 

[Fluff]

Glad to help & thanks for the feedback.

How can I like your post?

Simply click where it says Like this post, which is on the left hand side of the page, beneath the post.

 

[Mr2017]

Ok, done. Thank you.