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

New Member
Joined
Jul 5, 2018
Messages
29
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
JYNE4R5
JYNE4R5
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hey beloshi,

Try the below code, assuming your data starts in column A & you have a header in row 1


Rich (BB code):
Sub Clear_Blank_Rows()

Dim lRow As Long, lCol As Long

lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For x = lRow To 2 Step -1 If Cells(x, 1) = "" Then Rows(x).EntireRow.Delete Next x lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Range(Cells(2, 2), Cells(2, lCol)).Copy Range(Cells(3, 2), Cells(lRow, lCol)).PasteSpecial xlPasteAll Application.CutCopyMode = False End Sub
 
Last edited:
Upvote 0
Hey beloshi,

Try the below code, assuming your data starts in column A & you have a header in row 1


Rich (BB code):
Sub Clear_Blank_Rows()

Dim lRow As Long, lCol As Long

lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For x = lRow To 2 Step -1 If Cells(x, 1) = "" Then Rows(x).EntireRow.Delete Next x lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Range(Cells(2, 2), Cells(2, lCol)).Copy Range(Cells(3, 2), Cells(lRow, lCol)).PasteSpecial xlPasteAll Application.CutCopyMode = False End Sub

Thank you very much mse330 for the efforts, the actual data which I am concerned about starts from column C which includes vlookup formulas , so the basic purpose which I want to achieve with this macro is to see if there is null value in the Column B than remove the entire row, if Column B has any value 'xyz' 'bla bla' than it should copy the formulas from the above row including the format and use in the new row which has now value in COLUMN B.

So in short Check Column B if it has value than copy the above the formula including formatting
or else
If it is null / no value / blank than clear the entire row.

Login behing is may be that old data has been removed and new data has arrived but they don't have formula associated with them.

Keep in mind that there that the last also has is doing calculation and it should be neglected / ignored in the above formula.
 
Upvote 0
Sorry to add another post to the above as I have no EDIT Rights in the forum being a newbie so I am adding another reply to this thread.

The Core purpose / Logic behind this macro is that I have two sheets one has data and one calculation so this is being performed in %age sheet which is calculating all the ratios using different formulas including the SUM at the end. And data is coming from DATA Sheet which is in the same workbook. So new data comes every month into the Data sheet and it gets updated in the %age sheet every month manually but I want if there is new data the %age sheet update itself with this macro and if from the last month data something has been taken off that it should also be reflected in the %age sheet same for new added data rows in Data sheet.

Hope I am clear with my requirement here.

Thank you in advance

Best regards

Beloshi.
 
Upvote 0
Ok, so if we modify the code to consider the column that it searchs is B instead of A, give it a try

Code:
Sub Clear_Blank_Rows()

Dim lRow As Long, lCol As Long

lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

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

lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Range(Cells(2, 3), Cells(2, lCol)).Copy
Range(Cells(3, 3), Cells(lRow, lCol)).PasteSpecial xlPasteAll
Application.CutCopyMode = False

End Sub
 
Upvote 0
Ok, so if we modify the code to consider the column that it searchs is B instead of A, give it a try

Code:
Sub Clear_Blank_Rows()

Dim lRow As Long, lCol As Long

lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

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

lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Range(Cells(2, 3), Cells(2, lCol)).Copy
Range(Cells(3, 3), Cells(lRow, lCol)).PasteSpecial xlPasteAll
Application.CutCopyMode = False

End Sub

Thanks again for the efforts mse330 , Ok so Here is the feedback of the code I used it and the deletion part is working but the other part is not,

Where it should copy the formula from like range which is one row above from new Entry in COL B (for example range C11:M11 or select the above range with Formulas and FILL the remaining below new entry line before the END SUM (TOTAL).

best regards

Beloshi
 
Upvote 0
What happens when you run the code? Does your data start at row 2 (the formulas) ?
 
Upvote 0
What happens when you run the code? Does your data start at row 2 (the formulas) ?
Yes it start with the row 2 and I have header till ROW 4 and the real data which has formula starts from Row 5 Column C Ending with the SUM / TOTAL / AVERAGE Last line which also I don't want the macro to touch or ruin in anyway.
 
Upvote 0
Ok, can you try the below updated code

Code:
Sub Clear_Blank_Rows()
Dim lRow As Long, lCol As Long
lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
For x = lRow To 2 Step -1
    If Cells(x, 2) = "" Then Rows(x).EntireRow.Delete
Next x
lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range(Cells(5, 3), Cells(5, lCol)).Copy
Range(Cells(6, 3), Cells(lRow - 1, lCol)).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End Sub
 
Upvote 0
Ok, can you try the below updated code

Code:
Sub Clear_Blank_Rows()
Dim lRow As Long, lCol As Long
lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
For x = lRow To 2 Step -1
    If Cells(x, 2) = "" Then Rows(x).EntireRow.Delete
Next x
lRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range(Cells(5, 3), Cells(5, lCol)).Copy
Range(Cells(6, 3), Cells(lRow - 1, lCol)).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End Sub
Thank you once again for the efforts mse330 the code works like a charm It is doing as expected but the Last line which has total calculations Ʃ is also being affected by the routine and the formulas from the above are copied. we need to ignore the last two lines / rows
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top