Delete Range if cell in range is blank

[godlev]

Hi All,

i searched many posts but all of them seem to be for different cases.
i have sheet where i am pasting raw data table from column BA to CA

my data has random blank cells in column BY
i want to make macro to delete the range BA##:CA## where BY cell is blank

(i dont want to delete the whole row because i have other data in the rest of the columns A:AZ)
tried many examples but cant seem to be able to find proper way to delete only the range

can you help me on this?

[kev_]

[QUOTE]i want to make macro to delete the range BA##:CA## where BY cell is blank

Please Login or Register  to view this content.

[godlev]

i run the script but i get error message

Run-Time error '438':

Object doesn't support this property or method

debug.PNG

[kev_]

Note my edit
I mistyped ClearContents

[godlev]

it is deleting the content of the range but it is not shifting the rows up
maybe i couldn't express that i want to delete the cells in range BA:CA where BY is empty and then the shift the rest of the cells up

so if BY3 cell is empty the BA3:BY3 range to be deleted and the cells below to be shifted so there is no blank row left over

[kev_]

I am away from PC right now - so cannot test
- we may need to do this starting with last row - will test later

[kev_]

Here you go:

Please Login or Register  to view this content.

[godlev]

Worked like charm - Thank You!!!

[kev_]

Thanks for marking thread SOLVED and for the rep

[godlev]

Kev one last request - if i want to change the column range lets say range is A to Z and blank cells in C will determine deletion - i tried to modify it but coulnt figure out how to adjust the numbers

Please Login or Register  to view this content.

[kev_]

Column C offset -1 column is B , etc

Please Login or Register  to view this content.

[godlev]

Hi Kev - thanks for assisting me so far but it seems my raw data is having blank cells which are not really blank
attached a new example which i copied from the raw data and replaced the content with XXXXX
if you can see the cell C3 is completely blank and the B3 is displayed over it
The rest of the cells in C column seems to be blank but they are not really and the VBA is not detecting them as blank

[kev_]

I am away from PC for several days now and so cannot download your workbook.Limited to what I can manage via phone.
What does "not blank" mean? Do they contain an empty string or a formula resulting in empty string? Or something else?

[godlev]

i made a screenshot if it will help
when i select C2 which looks like empty but script does not detect it as empty
inside there is no nothing even single space

i guess somehow it is containing empty string as you said
if i double click the cell and then press enter without changing anything - then it becomes empty and the content of B2 is displayed over it (this is the usual behavior when excel cell string is wider then the width of the column)

Capture2.PNG

[kev_]

If cells not blank then SpecialCells method will not work
Looking at your picture some of the cells appear to be empty but others may not be.

Why use column C to determine last row?which column (A - Z) is most likely to contain a value in last row (unlikely to be C if using blanks in that to clear the others)
Try amending the column determining last row and see if that works (which it will if the cells are genuinely empty)

[kev_]

If cells not blank then SpecialCells method will not work
Looking at your picture some of the cells appear to be empty but others may not be.

Why use column C to determine last row?which column (A - Z) is most likely to contain a value in last row (unlikely to be C if using blanks in that to clear the others)
Try amending the column determining last row and see if that works (which it will if the cells are genuinely empty)

[kev_]

Busy for several hours now. Will check thread then.

[kev_]

Did you try what I suggested?

[kev_]

Possibly?

Please Login or Register  to view this content.