CODEWITHSUNDEEP
excelvba
Gehn47
Gehn47

Reputation: 93

Quick and easy macro to delete a single cell based on text criteria.

i just need to delete a single cell using a button.

So I am working on a project where by the user just hits a button and the spreadsheet prints based on the amount of data they put in. However we have one merged cell where they can put data in but if they dont enter any data, we need the cell to be empty. The snipped of code im using is below. The entire project is below that snippet for reference.

Range("B15:E19").Select
        If Range("B15:E19") = "Enter and special posting instruction here." Then
        Range("B15:E19").ClearContents
        End If

Option Explicit

Sub LastRowInOneColumn()

    Dim MySheet As Worksheet
    Set MySheet = Worksheets("SIF Sheet")

    With MySheet

        Dim xLastRow As Long
        xLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

        Select Case True

            Case xLastRow > 21 And xLastRow < 46

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=1

            Case xLastRow > 46 And xLastRow < 97

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=2

            Case xLastRow > 98 And xLastRow < 149

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=3

            Case xLastRow > 150 And xLastRow < 201

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=4

            Case xLastRow > 202 And xLastRow < 253

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=5

            Case xLastRow > 254 And xLastRow < 305

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=6

            Case xLastRow > 306 And xLastRow < 357

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=7

            Case xLastRow > 358 And xLastRow < 409

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=8

            Case xLastRow > 410 And xLastRow < 461

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=9

            Case xLastRow > 462 And xLastRow < 513

                'Application.Dialogs(xlDialogPrinterSetup).Show
                Worksheets("SIF Sheet").PrintOut From:=1, To:=10

        End Select




    End With

        Range("B15:E19").Select
        If Range("B15:E19") = "Enter and special posting instruction here." Then
        Range("B15:E19").ClearContents
        End If
End Sub

Upvotes: 2

Views: 48

Answers (2)

Karl Kristjansson
Karl Kristjansson

Reputation: 328

How About:

    If Range("B15") = "Enter and special posting instruction here." Then
        Range("B15:E19").ClearContents
    End If

Upvotes: 2

Calico
Calico

Reputation: 416

You could do the following. Just reference the top left of the merged range and set the value to ""

    If Range("B15").Value = "Enter and special posting instruction here." Then
          Range("B15").Value = ""
    End If

Hope that helps

Caleeco

Upvotes: 2

Related Questions