Deleting empty rows code doesn't respond

[Frankietheflyer]

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
-

Why doesn't the code work as

Part of the main code on sheet

"Clash Check"?​

​

​

<tbody>
</tbody>

- Why can't I call the Sub DeleteBlankRows on sheet "Clash Report" from sheet "Clash Check"?

The DeleteBlankRows Code is as follows

[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 .....

 [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

 

[Misca]

Looks like your DeleteBlankRows-macro is trying to reference the Clash Report sheet without actually referencing it. Without the "."s before the ranges your code is actually referencing the active sheet:

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

 

[Frankietheflyer]

Thanks for the quick response Misca

I've added the dots and added the code to the main code so it now runs through and nearly works. The main code copies and pastes correctly right up until ".Range("A2:C600").PasteSpecial xlPasteValues" but just after that at "addr = "A2:A600" & .Range("A1:D" & Rows.Count).End(xlUp).Row" it pastes information of the front sheet of the workbook (where the button is located) in column "A" of the "Clash Report".

The current code causing this issue is as follows:

 [FONT=Verdana]Public Sub Run_Clash_Check_Click()[/FONT]
[FONT=Verdana]'Application.ScreenUpdating = False[/FONT]
[FONT=Verdana]    Dim LastRow As Long
    Dim destRng As Range
Dim addr As String[/FONT]
[FONT=Verdana]'Application.ScreenUpdating = False
    
With Sheets("Clash Report")
.Range("A2:C600").Clear
End With[/FONT]
[FONT=Verdana]With Sheets("Clash Check")
.Range("V2:X4000").Clear
.Range("N2:P600").Copy[/FONT]
[FONT=Verdana]End With[/FONT]
[FONT=Verdana]With Sheets("Clash Report")[/FONT]
[FONT=Verdana].Range("A2:C600").PasteSpecial xlPasteValues[/FONT]
[FONT=Verdana]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
 [/FONT]
[FONT=Verdana]'Call Sheets("Clash Report").DeleteBlankRows[/FONT]
[FONT=Verdana]'Call DeleteBlankRows[/FONT]
[FONT=Verdana]End With
End Sub
[/FONT]

As an aside the above code still wouldn't call the "DeleteBlankRows" code when I left it as a separate code on the "Clash Report" sheet.

Quick update - The paste of the info from the front sheet comes at the line ".Range(addr).Value = Evaluate("IF(NOT(ISBLANK(" & addr & "))," & addr & ")")".
I think addr needs to be referenced to "Clash Report" somehow as the "With Sheets" doesn't seem to be doing it.
<strike></strike>

 

[Misca]

If you want your formula to refer to another sheet, you need to add the sheet reference to the formula. Since your addr is a string it only returns the range address of your range (ie. "$A$1:$B$10" or something like that). If your formula is located in Clash Report -sheet and you want it to refer to the cells in the "Clash Check" sheet, you should change the formula to something like

.Range(addr).Value = Evaluate("IF(NOT(ISBLANK('Clash Check'!" & addr & ")),'Clash Check'!" & addr & ")")

However, I'm not quite sure what the addr is supposed to be: At the moment it seems to be returning "A2:A6001" no matter what because the "Range."A1:D" & Rows.Count).End(xlUp).Row" is basically just a hard way to say "1". When you add "1" at the end of "A2:A600" you'll get "A2:A6001".

 

[Frankietheflyer]

Thanks again for the response Misca.

If I put just "A" in addr the code fails with application or object defined error after "

.Range(addr).Value = Evaluate("IF(NOT(ISBLANK('Clash Check'!" & addr & ")),'Clash Check'!" & addr & ")")​

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]"[/FONT] . If I put A:A in there it fails directly after the "addr=" line. If I put A2 in there it just deleted line 21 (??!!!). Having A2:A6000 makes it work so I left it there!

I'm now trying to add a further set of data to the line below that pasted in "Clash Report" which will need the blank lines taken out of that too. Currently have the addr cod in twice so the overall code pastes the first lot in and deletes the blanks, then pastes the second lot in below that and deletes all the blanks again. However, the second lot of pasting puts the data in with a huge gap (+300 odd rows) after the end of the first lot of data.

I've also discovered that the delete empty rows code has deleted loads of rows (and data) off the main sheet where the Active X Button is that fires the code!! (I was running it from the VB page!).

I think I need to have a total re-think!!

Thanks again for your inputs. Very much appreciated.

Frankie