Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 94
Default SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()


Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
I assume that there are some blank rows?

try this
dim found as range
set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).

if not found is nothing then
found.rows.delete
set found = nothing ' release memory
end ig


"Code Numpty" wrote:

I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()


Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 94
Default SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
Thank you Patrick
I must be missing something fairly simple here.

My range pen_list originally contains lookup formulas. The macro converts
the formulas to data and then sorts the columns so that all data is at the
top of the range. As I have converted formulas to data the remaining rows at
the bottom of the range are now blank - or at least I thought they were :-(

Your code gives me the no cells found error on the line

Set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks)



"Patrick Molloy" wrote:

I assume that there are some blank rows?

try this
dim found as range
set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).

if not found is nothing then
found.rows.delete
set found = nothing ' release memory
end ig


"Code Numpty" wrote:

I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()


Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
well as a trial, i had a column of data rangenamed, cleared the last ffew
rows and this workd exactly as written on the tin.

Are you sure that the cells are empty, maybe a space in there which yuo
can't see?

BUT, you're right. Interstingly, this failed when I copy/pastespecial. hmmmm

maby skip the cut/paste part?





"Code Numpty" wrote:

Thank you Patrick
I must be missing something fairly simple here.

My range pen_list originally contains lookup formulas. The macro converts
the formulas to data and then sorts the columns so that all data is at the
top of the range. As I have converted formulas to data the remaining rows at
the bottom of the range are now blank - or at least I thought they were :-(

Your code gives me the no cells found error on the line

Set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks)



"Patrick Molloy" wrote:

I assume that there are some blank rows?

try this
dim found as range
set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).

if not found is nothing then
found.rows.delete
set found = nothing ' release memory
end ig


"Code Numpty" wrote:

I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()


Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 94
Default SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
Hmm,
I used copy/paste special to get the data (simply numbers), rather than
formulas into the cells so that the data sort worked.

"Patrick Molloy" wrote:

well as a trial, i had a column of data rangenamed, cleared the last ffew
rows and this workd exactly as written on the tin.

Are you sure that the cells are empty, maybe a space in there which yuo
can't see?

BUT, you're right. Interstingly, this failed when I copy/pastespecial. hmmmm

maby skip the cut/paste part?





"Code Numpty" wrote:

Thank you Patrick
I must be missing something fairly simple here.

My range pen_list originally contains lookup formulas. The macro converts
the formulas to data and then sorts the columns so that all data is at the
top of the range. As I have converted formulas to data the remaining rows at
the bottom of the range are now blank - or at least I thought they were :-(

Your code gives me the no cells found error on the line

Set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks)



"Patrick Molloy" wrote:

I assume that there are some blank rows?

try this
dim found as range
set found =
Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).

if not found is nothing then
found.rows.delete
set found = nothing ' release memory
end ig


"Code Numpty" wrote:

I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()


Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default SpecialCells(xlCellTypeBlanks) = error 1004 No cells found
I'd check for empty cells with something like:

dim myRng as range
set myrng = nothing
on error resume next
set myrng = ....
on error goto 0

if myrng is nothing then
'do nothing
else
myrng.entirerow.delete
end if

or just turn off error checking before your delete statement.

On error resume next
worksheets....entirerow.delete
on error goto 0

================
Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D:D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

========
now those "empty" cells will really be empty and your .delete statement should
work.

Code Numpty wrote:

I have this macro to tidy up data in columns which leaves blank rows at the
end of the range. I want to delete the blank rows but the line

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

Results in error 1004 No Cells Found.

Here is the macro code
---------------------------------------------
Sub sort_disinfection_columns()

Worksheets("Disinfections").Range("pen_list").Copy
Worksheets("Disinfections").Range("pen_list").Past eSpecial
Paste:=xlPasteValues
Range("A3:A52").Sort Key1:=Range("A3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("B3:B52").Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("C3:C52").Sort Key1:=Range("C3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("D3:D52").Sort Key1:=Range("D3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("E3:E52").Sort Key1:=Range("E3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("F3:F52").Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("G3:G52").Sort Key1:=Range("G3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("H3:H52").Sort Key1:=Range("H3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("I3:I52").Sort Key1:=Range("I3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A1").Select

'Delete blank rows

Worksheets("Disinfections").Range("pen_list").Spec ialCells(xlCellTypeBlanks).EntireRow.Delete

'Format remaining rows

Range("pen_list").Select
With Selection.Font
.Name = "Arial Black"
.Size = 24
End With
Range("A1").Select
End Sub

---------------------------------------------

I've used this elsewhere with no problem so grateful for any help.

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Work around to SpecialCells(xlCellTypeBlanks)... DanF Excel Discussion (Misc queries) 7 June 29th 08 07:36 AM
Run-Time error '1004': No cells were found Regina[_2_] Excel Programming 13 November 28th 06 08:59 PM
Run-time error '1004': No cells were found Jamie Excel Programming 0 May 16th 05 11:01 PM
Run-time error '1004': No cells were found Jamie Excel Programming 0 May 16th 05 10:52 PM
specialcells(xlcelltypeblanks) Neil[_11_] Excel Programming 5 October 9th 03 10:11 AM


All times are GMT +1. The time now is 12:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement