Frankietheflyer
New Member
- Joined
- Nov 17, 2017
- Messages
- 30
Ok. Desperation is setting in!!!
I have two worksheets, "Clash Check" and "Clash Report". The sheet "Clash Check" gathers data which ends up as a list of names etc across 3 columns with lots of blank rows in it.
I have a code which can run through the list and take the blank rows out based on text in cells in column "A". The code works really well in my test workbook (just one sheet to sort through).
A code in Sheet "Clash Check" copies the list of names from N2:P600 and pastes it into the sheet "Clash Report" A2:C600.
I then want to delete all the blank rows in "Clash Report" A2:C600, but try as I may the code won't work when I put it in with the Copy / Paste bit.
I then moved the delete rows code to the "Clash Report" sheet where it runs successfully, but it won't call from the code in the "Clash Check" sheet as "Call DeleteBlankRows" gives me a "Sub or Function not defined" error.
I've tried using the "Call Sheets("Clash Report"). DeleteBlankRows", but it them fails with "Object doesn't support this property or method".
Questions
-
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
- Why can't I call the Sub DeleteBlankRows on sheet "Clash Report" from sheet "Clash Check"?
The DeleteBlankRows Code is as follows
The full code is run from a button and is .....
Thanks in advance
Frankie
I have two worksheets, "Clash Check" and "Clash Report". The sheet "Clash Check" gathers data which ends up as a list of names etc across 3 columns with lots of blank rows in it.
I have a code which can run through the list and take the blank rows out based on text in cells in column "A". The code works really well in my test workbook (just one sheet to sort through).
A code in Sheet "Clash Check" copies the list of names from N2:P600 and pastes it into the sheet "Clash Report" A2:C600.
I then want to delete all the blank rows in "Clash Report" A2:C600, but try as I may the code won't work when I put it in with the Copy / Paste bit.
I then moved the delete rows code to the "Clash Report" sheet where it runs successfully, but it won't call from the code in the "Clash Check" sheet as "Call DeleteBlankRows" gives me a "Sub or Function not defined" error.
I've tried using the "Call Sheets("Clash Report"). DeleteBlankRows", but it them fails with "Object doesn't support this property or method".
Questions
-
Why doesn't the code work as
[TABLE="width: 778"]Part of the main code on sheet
"Clash Check"?
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
- Why can't I call the Sub DeleteBlankRows on sheet "Clash Report" from sheet "Clash Check"?
The DeleteBlankRows Code is as follows
Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub DeleteBlankRows()
Dim addr As String
With Sheets("Clash Report")
addr = "A2:A600" & Range("A1:D" & Rows.Count).End(xlUp).Row
Range(addr).Value = Range(addr).Value
Range(addr).Value = Evaluate("IF(NOT(ISBLANK(" & addr & "))," & addr & ")")
Range(addr).SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
End With
End Sub
The full code is run from a button and is .....
Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Public Sub Run_Clash_Check_Click()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim LastRow As Long
Dim destRng As Range
Dim addr As String
[LEFT][COLOR=#222222][FONT=Verdana]Application.ScreenUpdating = False[/FONT][/COLOR][/LEFT]<strike></strike>
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With Sheets("Clash Report")
.Range("A2:C600").Clear
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With Sheets("Clash Check")
.Range("V2:X4000").Clear
.Range("N2:P600").Copy[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With Sheets("Clash Report")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif].Range("A2:C600").PasteSpecial xlPasteValues
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Call Sheets("Clash Report").DeleteBlankRows[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'Call DeleteBlankRows[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End With
[LEFT][COLOR=#222222][FONT=Verdana]Application.ScreenUpdating = True[/FONT][/COLOR][/LEFT]
End Sub[/FONT]
[/FONT]<strike></strike>
Thanks in advance
Frankie
Last edited by a moderator: