PDA

View Full Version : [SOLVED] vba code for remove blank cells



abbccc
05-04-2013, 01:42 AM
I want remove blank cells column a and show result in column b
please give me a vba code
for example
9981

GarysStudent
05-04-2013, 04:32 AM
Try this:


Sub dural()
Dim N As Long, NN As Long, K As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
K = 1
For NN = 1 To N
If Cells(NN, 1) <> "" Then
Cells(K, 2) = Cells(NN, 1)
K = K + 1
End If
Next
End Sub

HaHoBe
05-04-2013, 04:52 AM
Hi, abbccc,

if the shown are values (no formulas) you may shorten the loop to only those cells with values like


Sub abbccc_2()
Dim rngCell As Range
Dim lngTarg As Long
lngTarg = 1
For Each rngCell In Range("A:A").SpecialCells(xlCellTypeConstants, 23)
Cells(lngTarg, 2) = rngCell
lngTarg = lngTarg + 1
Next rngCell
End Sub Ciao,
Holger

abbccc
05-04-2013, 06:19 AM
Thank you HaHoBe :friends: :friends:
Thank you GarysStudent :friends: :friends:
:rotlaugh: :rotlaugh:

shrivallabha
05-04-2013, 10:36 AM
Or non-looper [frequently shown by snb]

Option Explicit
Public Sub GetNonBlanks()
Dim r As Range
Dim v As Variant
Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
v = Filter(Application.Transpose(Evaluate("=IF(" & r.Address & "<>""""," & r.Address & ",""~"")")), "~", False)
Range("B1").Resize(UBound(v) + 1, 1).Value = Application.Transpose(v)
End Sub

Paul_Hossler
05-04-2013, 11:52 AM
no error checking and as is will only work with constant cells but both could easily be added



Sub test()
Call Columns("A:A").SpecialCells(xlCellTypeConstants, 23).Copy(Range("B1"))
Application.CutCopyMode = False
End Sub



Paul

snb
05-04-2013, 02:25 PM
Sub M_snb002()
With Columns(1)
.AutoFilter 1, "<>"
.Cut Cells(1, 2)
.AutoFilter
End With
End Sub

or


Sub M_snb003()
Columns(1).SpecialCells(4).EntireRow.Delete
Columns(1).Insert
End Sub

abbccc
05-04-2013, 10:55 PM
Thank you for all

SendGroupSMS
01-20-2023, 09:31 PM
Is the VBA code provided in the solution worked for all the latest as well as old versions of MS Excel?

Aussiebear
01-21-2023, 03:22 AM
Is the VBA code provided in the solution worked for all the latest as well as old versions of MS Excel?

Did you try it first?