Delete blank columns

[Johannes_R]

Hi,

I have linked two sheets and would like to delete all blanks in the second
sheets.

I'm using this macro:
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "" Then
' rows(RowNdx).Delete
Rows(RowNdx).EntireRow.Hidden = True
End If
Next RowNdx
End Sub

I want to make two adjusments to this macro:

1. This macro hides the blank columns- I want to delete them
2. I would like it to update automatically when I update the sheets
3. I would like the numbering to follow with the filled columns

Is this possible?

Appreciate all the help I can get!

 

[Jacob Skaria]

You have not specified in which sheet you need to delete rows. Replace
ActiveSheet in the code with Sheets("Sheet2") if you want to delete rows in
sheet2

1. This macro hides the blank columns- I want to delete them

Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

2. I would like it to update automatically when I update the sheets

Launch VBE using Alt+F11. Double click 'This Workbook' and drop down to get
this event and paste the above code under this ; which would update sheet2 on
Save.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
End Sub
If you want to change it as soon as you make changes in sheet1 then double
click Sheet1 in VBE and dropdown to find the below and paste the code.
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

3. I would like the numbering to follow with the filled columns

The best way is to put the formula in Column A of Sheet2 and copy that to
all rows in Column A. This will autoadjust the numbering
= Row()
=Row()+1

 

[Archimedes' Lever]

If this post helps click Yes

Whoever the total retard is that made this stupid little sig addendum
to you guys' posts is about as stupid as it gets. There are NO BUTTONS
in Usenet!

So why would an IDIOT programmer add a TEXT based sig to his retarded
GUI interface, which he wrongfully assumed that everyone uses?

Most folks read this group in TEXT mode.