VBA wont work due to merged cells

[impressive]

Hello everyone,

I am working on a list where the Area from A1 to Q7 is basically the header (Client Name, Location, Project Start, Project End, etc...), which consists mostly of merged cells.

In this list are items listed that a client gets.

So the listing of the Items starts from A8, and this is also where I want my Macro to work,

I have a macro that should delete empty rows if a Cell in the A-Column is empty.

This is what i have so far:

Sub DeleteRow()

Range("a8:a257").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

When I execute it, I get an error message and in the debug is written:
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

After i removed in my sheet the header area with the merged cells (A1 to Q7) , it worked perfectly fine.

Could someone please help me?

Kind regards

 

[Georgiboy]

Maybe just remove the merged cells and replace with a format called 'Center Across Selection'

If you select the cells you want to merge and then:
Right click
Format Cells
Select the Alignment tab
Select 'Center Across Selection' in the 'Horizontal' dropdown
Click 'OK'

It gives the same look as a merged cell although it will not be merged.

Avoid merged cells in general - they can be very problematic

 

[HaHoBe]

Hi,

one of the worst "features" in Excel I agree.

Did you check there were any empty cells in the area you mentioned? If not that codeline will raise an error. Maybe use something like

With Range("A8:A257")
  If WorksheetFunction.CountBlank(.Cells) > 0 Then
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End If
End With

as MergedCells should only cause problems when working with Columns as that would delete all columns except the one you wanted to be delete.

Like Georgiboy mentioned: get rid of Merged Cells.

Holger

 

[impressive]

Hi,

thank you very much for posting your ideas.

I used now the following:

Sub DeleteRow_SOW()
With Range("A8:A257")
If WorksheetFunction.CountBlank(.Cells) > 0 Then
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End With

I get the error:
Sub DeleteRow_SOW()

Is it not possible to tell excel from which line the marco should start? That would be maybe too easy

 

[impressive]

Ok so i unmerged now the row A to C.

This works now.

I gues I need to redesign the header.

Thank you guys for your help.

One question that is still left:
How do I learn VBA?
Can you give me a good source?

Because I am really starting from scratch and I have nothing to do with programming.

Kind regards

 

[HaHoBe]

Hi,

in the future please use code-tags for displaying your procedures.

What error number is shown?

You advised the code to start in Row 8 by

With Range("A8:A257")

You could also use

With Range(Cells(8, "A"), Cells(257, "A"))

Holger

 

[Georgiboy]

One question that is still left:
How do I learn VBA?
Can you give me a good source?

I enjoyed the book: Excel VBA for Dummies

I read the 2007 version but there are others now - i just find the for dummies series good for starting out.

 

[shinigamilight]

Ok so i unmerged now the row A to C.

This works now.

I gues I need to redesign the header.

Thank you guys for your help.

One question that is still left:
How do I learn VBA?
Can you give me a good source?

Because I am really starting from scratch and I have nothing to do with programming.

Kind regards

Excel VBA Introduction

Excel VBA is a powerful (and fun!) tool which allows you to automate your Excel workbooks. These videos will help you through your first steps on the way to ...

www.youtube.com

 

[impressive]

I hope I quoted it now the right way:
So this is the code I used:

Sub DeleteRow_SOW()

    With Range("a8:a257").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

The error message is the following:
Compile error: Expected end with

And then in the editor:

 

[Georgiboy]

Every With needs an End With, also you can avoid selecting - see below

Sub DeleteRow_SOW()

    With Range("a8:a257")
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub