Delete blank cells and 2 adjacent cells to the left of blank cells within a worksheet

Hi guys,

Here's hoping somebody can assist in helping me modify an existing code shown below.
The current code deletes unwanted "." and adjacent cells within a workseet

The worksheet is created via another programme and the unwanted "." have now been replaced with blank cells.

I am wondering if there is a way to replace the "." with "blank cells" somehow

Please Login or Register  to view this content.

Many thanks for your time
Andy

Hi PCI

I have tried to attach a copy of what I am trying to achieve, not sure if it has attached or not as I don't see it anywhere. I went to the advanced tab and used the manage attachments to select the one I want it is called Test.xslm

The file has 3 tabs, Before, After and New

I have highlighted the rows that I am trying to delete in yellow and they have a full stop in the percentage column. The current macro will delete the relevant rows and the results will be found shown in the After tab. I have also included a New tab that has blank cells instead of the full stops and this is what I am trying to delete

Thanks Andy

Hi PCI,

The first row is always 19 but the D & G will continue indefinitely e.g it will continue for every 3rd row every week as additional data is added.

It will be D, G, J, M, P etc.

If there is a way to find last row and column and then delete the blank cells and adjacent 2 cells to the left that is what I am looking to do.

Cheers

Paste next demonstration to the Sheet6 worksheet module
as like this in a standard module it works only with the active worksheet :

PHP Code: 

Sub Demo1()
      Dim C%, Rg As Range
          Application.ScreenUpdating = False
    For C = 4 To Cells(19, Columns.Count).End(xlToLeft).Column Step 3
          Set Rg = Cells(Rows.Count, C).End(xlUp)
        While Rg.Row > 21
           If Rg(0).Value = "" Then Rg(0, -1).Resize(, 3).Delete xlShiftUp
          Set Rg = Rg.End(xlUp)
        Wend
    Next
          Set Rg = Nothing
          Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Depending on the amount of data you have, this might be faster on a large amount of data.
If you want to try it, try it on a copy of your original first.

Please Login or Register  to view this content.

This is still for the sheet with the periods (full stops).
If you want it for the sheet with the empty cells, let us know.

This should work on the empty cells. (Try on a copy of the original first)

Please Login or Register  to view this content.

Hi Guys,

Thanks for your help and valued answers, all answers work brilliant. I am spoilt for choice but will use the answers to try and further my excel knowledge.

Thanks again
Andy