Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-11-2016, 11:01 AM
H28Sailor H28Sailor is offline Macro functioning correctly Windows 7 32bit Macro functioning correctly Office 2007
Advanced Beginner
Macro functioning correctly
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default Macro functioning correctly

I have the following code:-



The initial part of the routine IS working perfectly, but the last part of the VBA routine is not working - i.e. the deleting rows aspect
The 'Copy Data' button sits on the first two rows
Any advice on how to debug would be welcome


Code:
Sub CopyRows()
' copy all rows of data in worksheet 'temp' to the appropriate worksheets
     Dim iLooper As Long, NextRow As Long, ws As Worksheet, strSheet As String
     With Worksheets("temp")
         For iLooper = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
             If UCase(Trim(.Cells(iLooper, "B").Value)) Like "ENROLLMENT*" Then
                 strSheet = "ENROLLMENT"
             ElseIf UCase(Trim(.Cells(iLooper, "B").Value)) Like "WEEK*" Then
                 strSheet = .Cells(iLooper, "a").Text
             Else
                 strSheet = ""
             End If
             If strSheet <> "" Then
                 Set ws = Worksheets(strSheet)
                 NextRow = ws.Columns("B").Find("*", , xlValues, , 1, 2).Row + 1
                 ws.Rows(NextRow).Insert
                 ws.Cells(NextRow, "A").Resize(, 8).Value = _
                       .Cells(iLooper, "A").Resize(, 8).Value
             End If
         Next
     End With
 ' delete all rows of data in worksheet 'temp'
 Dim r As Long For r = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
   If Cells(r, 1) = "" Then Rows(r).Delete
 Next r
 End Sub
Reply With Quote
  #2  
Old 12-11-2016, 03:13 PM
NoSparks NoSparks is offline Macro functioning correctly Windows 7 64bit Macro functioning correctly Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Can you elaborate as to what "is not working" means and why you say it's not working ?

The comment in the code says... ' delete all rows of data in worksheet 'temp'
but the code is written to delete only rows where column A cell is empty.
Reply With Quote
  #3  
Old 12-11-2016, 03:18 PM
H28Sailor H28Sailor is offline Macro functioning correctly Windows 7 32bit Macro functioning correctly Office 2007
Advanced Beginner
Macro functioning correctly
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default

Thank you
I guess it is too early in the morning for me...............
removing the 'If" condition allows it to do what I want

Many thanks
Bob M
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to invoke the macro correctly in the workflow smndnm Word VBA 6 07-08-2014 03:13 AM
Macro functioning correctly Figure Table not functioning properly justphilip2003 Word 1 01-21-2014 01:15 PM
Image proportional resize not functioning! alexfcm PowerPoint 0 09-27-2012 07:22 AM
Macro functioning correctly Insert->Object feature not functioning when Word is saved as PDF amrita Word 2 04-20-2010 02:42 PM
Outlook not functioning bordterr Outlook 1 12-17-2009 11:05 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft