If a cell (in a column) is blank clear the content of the entire row and if there is a new entry paste the format of the previous column range down to

[beloshi]

Hello dear experts,

I am new to the VBA Scene and I wanted to know that I am stuck with a problem where I have to do two tasks at once in a VBA (Macro). Please find the attached picture to understand the situation more.
https://imgur.com/a/JYNE4R5

1. First thing is I want to check the column and see if ther eis a blank entry so want to remove the entire column.
2. see 20|ABC if there is a value in the column than I want the code to Copy the formulas / Format from the above cell / row to the next one.

 

[mse330]

Glad to help ... Just change the line below. lRow - 1 change it to lRow - 2

Range(Cells(6, 3), Cells(lRow - 1, lCol)).PasteSpecial xlPasteAll
​

 

[beloshi]

Glad to help ... Just change the line below. lRow - 1 change it to lRow - 2

Range(Cells(6, 3), Cells(lRow - 1, lCol)).PasteSpecial xlPasteAll
​

Thanks once again you have saved alot of hassle for me. It worked like a charm.

A quick question as I understood it is copying everything, can I put copy formula and only paste formula leaving the format intact. Hope you understand my point.

And

2nd thing if I only want to use the second part that is copy and pasting function not the delete function where should I start and end

Thanks and much appreciated.

best regards

Beloshi

 

[mse330]

You are welcome

Regarding your first point, you can change the "xlPasteAll" to "xlPasteFormulas"

If you don't want to delete any rows, you can simply delete/comment the below part of the code

For x = lRow To 2 Step -1
    If Cells(x, 2) = "" Then Rows(x).EntireRow.Delete
Next x

 

[beloshi]

You are welcome

Regarding your first point, you can change the "xlPasteAll" to "xlPasteFormulas"

If you don't want to delete any rows, you can simply delete/comment the below part of the code

For x = lRow To 2 Step -1
    If Cells(x, 2) = "" Then Rows(x).EntireRow.Delete
Next x

Thats is getting better and better now.

Quick Query:

I have freeze panes in some sheets and I want a code to put in the end after doing everything, cursor position in every sheet in the Workbook should be at A1 and scroll position also reset (for example Column A B C D ) not where I left it (For example Column AG AH AI ....)

much appreciated

Kind Regards

Beloshi

 

[mse330]

You can place the below code in "ThisWorkBook" which will scroll all sheets to A1 once the workbook is opened … Though I am not really familiar what will happen with the freeze panes though

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In Worksheets
    Application.Goto Reference:=ws.Range("A1"), Scroll:=True
Next

End Sub