• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA Code to delete line in excel|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA Code to delete line in excel|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosVBA Code to delete line in excel
sp_PrintTopic sp_TopicIcon
VBA Code to delete line in excel
Avatar
Curt Hasan

Active Member
Members

Dashboards
Level 0
Forum Posts: 5
Member Since:
May 31, 2016
sp_UserOfflineSmall Offline
1
December 26, 2018 - 1:30 pm
sp_Permalink sp_Print

I need to a code that will delete the first 6 rows of a spreadsheet and find the row that has string "HOURS AND RATES"  and delete that row and the next 15 row beneath it.

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
December 26, 2018 - 2:17 pm
sp_Permalink sp_Print sp_EditHistory

Hi Curt

In which column is the string "HOURS AND RATES" located?

The below code assumes that it is in column A. Change if it is in another column

Sub DeleteRows()
Application.ScreenUpdating = False
Dim rw As Long
'Delete first 6 rows
ActiveSheet.Rows("1:6").Delete
'Find text and delete 16 rows
'Change the column if necessary
rw = Application.Match("HOURS AND RATES", Range("A:A"), 0)
ActiveSheet.Rows(rw & ":" & rw + 15).Delete
Application.ScreenUpdating = True
End Sub

Avatar
Curt Hasan

Active Member
Members

Dashboards
Level 0
Forum Posts: 5
Member Since:
May 31, 2016
sp_UserOfflineSmall Offline
3
December 26, 2018 - 3:25 pm
sp_Permalink sp_Print

Column is correct. Do I need to specify a specific sheet name because it will be applied to multiple files in a folder?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
December 26, 2018 - 4:09 pm
sp_Permalink sp_Print

The macro works on the active sheet.

Lets say the macro is saved in Workbook A.

You open Workbook A then open Workbook B.

Select the sheet to delete the rows in Workbook B and then run the macro from the Developer tab.

Hope this helps.

Avatar
Curt Hasan

Active Member
Members

Dashboards
Level 0
Forum Posts: 5
Member Since:
May 31, 2016
sp_UserOfflineSmall Offline
5
December 26, 2018 - 6:41 pm
sp_Permalink sp_Print

It does help, but I a little confused.  I have a routine that transforms files within a folder that a friend helped.  I wanted to insert the above code to be ran at the same time. So, where would the provided code be inserted? 

My Routine:

Option Explicit

Private Sub CommandButton1_Click()
Dim FileSystem As Object
Dim SourceFolder As String, ResultFolder As String
Dim Folder As Object
Dim File As Object

SourceFolder = Range("B1").Value
If Right(SourceFolder, 1) <> "\" Then SourceFolder = SourceFolder & "\"
ResultFolder = Range("B2").Value
If Right(ResultFolder, 1) <> "\" Then ResultFolder = ResultFolder & "\"

Set FileSystem = CreateObject("Scripting.FileSystemObject")

Set Folder = FileSystem.GetFolder(SourceFolder)
For Each File In Folder.Files
If File.Name Like "*.xlsx" Then
modify File.Path
File.Move ResultFolder
DoEvents
End If
Next File

End Sub

 

Sub modify(Fname As String)
Dim i As Long, srd As String
Dim M As Variant
Dim wb As Workbook
Dim q As Integer
M = Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
Set wb = Workbooks.Open(Fname)

With wb.Worksheets(1)
.Cells.UnMerge

i = 1
Do
If .Cells(i, 1) = "SRD" Then
.Cells(i, 17) = "YEAR"
.Cells(i, 18) = "MONTH"
.Cells(i, 19) = "DATE"
.Cells(i, 20) = "BASE"
.Cells(i, 21) = "FLEET"
Exit Do
End If
i = i + 1
Loop
i = i + 1
Do While .Cells(i, 2) <> ""
srd = .Cells(i, 1)
.Cells(i, 17) = Left(.Cells(1, 8), 4)
.Cells(i, 18) = M(Int(Right(.Cells(1, 8), 2)) - 1)
.Cells(i, 19) = DateSerial(Left(.Cells(1, 8), 4), Right(.Cells(1, 8), 2), 1)
.Cells(i, 20) = .Cells(1, 2)
.Cells(i, 21) = .Cells(1, 4)
i = i + 1
Do While .Cells(i, 1) = "" And .Cells(i, 2) <> ""
.Cells(i, 1) = srd
.Cells(i, 17) = Left(.Cells(1, 8), 4)
.Cells(i, 18) = M(Int(Right(.Cells(1, 8), 2)) - 1)
.Cells(i, 19) = DateSerial(Left(.Cells(1, 8), 4), Right(.Cells(1, 8), 2), 1)
.Cells(i, 20) = .Cells(1, 2)
.Cells(i, 21) = .Cells(1, 4)
i = i + 1
Loop
Loop
End With
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
December 27, 2018 - 9:24 am
sp_Permalink sp_Print

Hi Curt

Try this but I suggest you use sample files as your codes above saves and override your original files (very dangerous)

1) Add this to the top of your code in the MODIFY sub module with the rest of the DIM

Dim rw As Long

2) Adding this code after the two Loop,Loop above

Loop

Loop

.Rows("1:6").Delete
rw = Application.Match("HOURS AND RATES", .Range("A:A"), 0)
.Rows(rw & ":" & rw + 15).Delete

WARNING : The code have not been tested. Please test it on sample files.

Sunny

sp_AnswersTopicAnswer
Answers Post
Avatar
Curt Hasan

Active Member
Members

Dashboards
Level 0
Forum Posts: 5
Member Since:
May 31, 2016
sp_UserOfflineSmall Offline
7
December 27, 2018 - 1:45 pm
sp_Permalink sp_Print

Thanks it works exactly the way I want.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
December 27, 2018 - 2:17 pm
sp_Permalink sp_Print

Thanks for your feedback.

Happy to know it works.

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard Benson-King
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.